0

I am trying to copy data from wsCL2 in Column A starting from cell A2 down to whenever the data ends; the number of rows can vary.

I want to paste into Column A of coSummary, from the next empty cell in that column. 

Sub Test()
    Dim wb As Workbook
    Dim wsCL2 As Worksheet
    Dim summary As Worksheet
    Dim colLastRow As Long
    Dim summaryLastRow As Long

    Set wb = ActiveWorkbook
    Set wsCL2 = wb.Sheets("Tocopyfrom")
    Set summary = wb.Sheets("Summary")

    'Defining last row of data in Colliers
    summaryLastRow = summary.Cells(Rows.Count, 1).End(xlUp).Row
    colLastRow = wsCL2.Cells(Rows.Count, 1).End(xlUp).Row

    summary.Cells(10, 10).Value = colLastRow
    summary.Cells(11, 10).Value = summaryLastRow

    'Copying range from Colliers to Summary Page  (***ERROR HERE***)
    wsCL2.Range(Cells(2, "A").Address(), Cells(colLastRow, "A")) _
        .Copy summary.Cells(summaryLastRow + 1, "A")

    colLastRow = summary.Cells(Rows.Count, 1).End(xlUp).Row
End Sub

It sometimes throws an error when trying the Copy method; other times it will execute properly.

I suspect it's related to refreshing the values for the summaryLastRow value or to D?

Thanks!

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
Vish B
  • 1
  • 1

1 Answers1

1

You have an issue in this line of code:

wsCL2.Range(Cells(2, "A").Address(), Cells(colLastRow, "A")).Copy summary.Cells(summaryLastRow + 1, "A")

You use a lot of "Cells" which do not reference to wsCL2 but to your ActiveSheet, which is probably not what you want. This is the correct way to do it:

wsCL2.Range(wsCL2.Cells(2, "A").Address(), wsCL2.Cells(colLastRow, "A")).Copy summary.Cells(summaryLastRow + 1, "A")

For better understanding, this is what your code is actually saying:

wsCL2.Range(ActiveSheet.Cells(2, "A").Address(), ActiveSheet.Cells(colLastRow, "A")).Copy summary.Cells(summaryLastRow + 1, "A")

Edit: I'm also not sure about how you're using these references once with .Address: Cells(2, "A").Address() and next up without: Cells(colLastRow, "A") A better way to reference ranges in my opinion is by using just digits:

wsCL2.Range( wsCL2.Cells(2,1), wsCL2.Cells(colLastRow,1) )

Final Edit: One other thing, if you're tired of writing wsCL2.

with wsCl2
  .Range( .Cells(2,1), .Cells(colLastRow,1) )
end with
Rob
  • 101
  • 5
  • Thank you very much for the very prompt response, I'll give it a go and get back to you! – Vish B Aug 31 '18 at 11:18
  • Wow that's absolutely amazing! It works perfectly now, thank you for the great explanation of what was going wrong with the code! – Vish B Aug 31 '18 at 11:28
  • An "accepted answer" marking would be nice :-) I've also added another info/code snippet into my post above. – Rob Sep 13 '18 at 07:55