VBA Copying Range from Other Worksheet

Question

Trying to copy the last 3 cells of Range A and then paste them into a new sheet called Report. Keep getting a compile error: Invalid or unqualified reference. I know the paste code is working but my copy code does not. Is it because .Cells requires it in a With loop? If so, I can't figure out how to correct it. This is the code in question:

Sheets("DATA").Activate

With Sheets("DATA")
    ' Use this lRow now since the previous ones require you to be in a With loop
    Range(.Cells(lRow - 2, 2), .Cells(lRow, 2)).Copy
End With

With Sheets("Report")
    .Activate
    ' Pastes the last 3 cells of Column A into the Month column
    .Range("B13").PasteSpecial Paste:=xlPasteAll
    .Range("B12").Formula = "Month"
    .Range("C12").Formula = "Production Cost"
    'Calculates the production cost and places it in the correct column

    .Range("D12").Formula = "Inventory Cost"
    .Range("E12").Formula = "Total Cost"
    .Range("B16").Formula = "Total"
 End With

Range(.Cells(lRow - 2, 2), .Cells(lRow, 2)).Copy has no meaning except the case it is inside a With ... End With loop... Try Range(Cells(lRow - 2, 2), Cells(lRow, 2)).Copy. But the best idea is to better define the range, using its sheet. Something like that: Dim shDat as Worksheet followed by Set shDat = Sheets("DATA"). Then, no need to activate. You can use it like shData.Range(shData.Cells(lRow - 2, 2), shData.Cells(lRow, 2)).Copy. Activating, Selecting must be avoided except the cases when it is not possible to do that. Now, it is possible and recommended... — FaneDuru

NautMeg NautMeg · Accepted Answer · 2020-02-27T20:50:05

your code Range(.Cells(lRow - 2, 2), .Cells(lRow, 2)).Copy isn't within a [With] statement, therefore the [.Cells] reference will be invalid.

VBA Copying Range from Other Worksheet

1 Answers