Excel VBA Copy/Paste Error

Question

I'm attempting to consolidate data from multiple worksheets into one, using a method described here.

The difference is I only want a single column of data from each file copied so I have modified my code thus:

With wsMaster
     erow = .Range("B" & .Rows.Count).End(xlUp).Row 'get the last row in column B
     '~~> Copy from the file you opened
     wsTemp.Range("A2:A" & .Rows.Count).Copy 'copy data in column A starting at row 2
     'to however long the data runs for
     '~~> Paste on your master sheet below last used row in column B
     .Range("B" & erow).Offset(1, 0).PasteSpecial xlPasteValues
End With

This works for the first iteration (it copies the first source file correctly) but when it gets to the second source file it throws an error "Run-time error '1004': PasteSpecial method or Range class failed".

When in debug mode, I try to manually click and Ctrl+V the copied data, it says the size/shape of the ranges don't match. HOWEVER - if I click in any cell at or above Row 2, it pastes the data it has copied in the macro, without complaining.

Any ideas?

Answer 1

wsTemp.Range("A2:A" & .Rows.Count).Copy

This line causes problem, please chenge this to

wsTemp.Range("A2:A" & Range("A2").SpecialCells(xlCellTypeLastCell).Row).Copy

Answer 2

When you are nested inside the With wsMaster it means all objects that you add the prefix . inside the With statement, are related to this object (wsMaster worksheet).

So your first place where you are trying to look for the last row is correct

erow = .Range("B" & .Rows.Count).End(xlUp).Row 'get the last row in column B

But the second place, where you are trying to copy from a different sheet (from wsTemp worksheet), is not correct. The line below:

wsTemp.Range("A2:A" & .Rows.Count).Copy 'copy data in column A starting at row 2

Means that .Rows.Count is looking for count of rows in wsMaster sheet (and not wsTemp).

So you need to change that line to:

wsTemp.Range("A2:A" & wsTemp.Rows.Count).Copy

I prefer the following syntax to copy all occupied cells in Column "A":

wsTemp.Range("A2:A" & wsTemp.Cells(wsTemp.Rows.Count, "A").End(xlUp).Row).Copy