vba - Error on "xlPasteColumnwidths" and can my code be more efficient?

Question

What is the best way to copy data between workbooks? I have written the code below for copying data from a given workbook to "thisworkbook". However I'm facing some problems and I am starting to think this is not the correct way to do this?

What do I exactly want:

I have a workbook in which I have lots of data, I want some given ranges copied from that workbook into "thisworkbook". After these are copied I want to delete all blank rows between the data

What I have:

Application.ScreenUpdating = False

Dim wb As Workbook

Set wb = Workbooks.Open("path")

With wb
    Sheets("sheet1").Range("B3:D107").Copy
End With

With ThisWorkbook.Sheets("sheet1").Range("A1")
    .PasteSpecial xlPasteAll
    .PasteSpecial xlPasteColumnWidths
End With

With wb
    Sheets("sheet1").Range("B113:D117").Copy
End With

With ThisWorkbook.Sheets("sheet1").Range("A106")
    .PasteSpecial xlPasteAll
    .PasteSpecial xlPasteColumnWidths
End With

With wb
    Sheets("sheet1").Range("F3:F107").Copy
End With

With ThisWorkbook.Sheets("sheet1").Range("D1")
    .PasteSpecial xlPasteAll
    .PasteSpecial xlPasteColumnWidths
End With

With wb
    Sheets("sheet1").Range("F113:F117").Copy
End With

With ThisWorkbook.Sheets("sheet1").Range("D106")
    .PasteSpecial xlPasteAll
    .PasteSpecial xlPasteColumnWidths
End With

wb.Close False

On Error Resume Next

Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Application.ScreenUpdating = True

Why do I get an error at "xlPasteColumnwidths and can I make this code more efficient?

Answer 1

You can't have multiple enumerations in a PasteSpecial operation, you'll need to perform each operation separately:

With wb
    Sheets("sheet1").Range("B3:D107").Copy
End With

With ThisWorkbook.Sheets("sheet1").Range("A1")
    .PasteSpecial xlPasteAll
End With

With wb
    Sheets("sheet1").Range("B3:D107").Copy
End With

With ThisWorkbook.Sheets("sheet1").Range("A106")
    .PasteSpecial xlPasteColumnWidths
End With

Regarding performance I can't see any issue with your code. Sure, you could look at picking up the whole sheet into an array and then writing out the bits you need but that would be overkill for something this simple.