VBA - Copy & Paste from onesheet to another using range function

Question

I am trying to copy a 4 columns with its data from GraphColumns sheet (has 2666 rows) to another sheet within the same workbook called Graph Data. I want to paste it at the last row of Graph Data which is 12155. My code is as follows:

Sub C_P()
Dim lastrow As Integer

lastrow = Range("A" & Rows.Count).End(xlUp).Row


LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Range("A2:D" & LR).Copy Destination:=Sheets("Graph Data").Range("A12155")

End Sub

My lastrow isn't doing anything which I am aware off. I just want the macro to paste the .Range("last row of Graph Data") at A12155 without me calling it because I'll need it for future data as we progress.

You may have an issue with unqualified ranges. Have you checked the value of LR? It's also unclear why you have two last row variables (lastrow & LR) — urdearboy

urdearboy urdearboy · Accepted Answer · 2021-02-25T17:05:01

Maybe you are looking for something like:

Sub C_P()

'Declare worksheets and variables
Dim GD As Worksheet: Set GD = ThisWorkbook.Sheets("Graph Data")
Dim GC As Worksheet: Set GC = ThisWorkbook.Sheets("Graph Columns")
Dim lr As Long

'Determine Graph Columns Range Size & Copy
lr = GC.Range("A" & GC.Rows.Count).End(xlUp).Row
GC.Range("A2:D" & lr).Copy

'Paste in first blank row on Graph Data
GD.Range("A" & (GD.Range("A" & GD.Rows.Count).End(xlUp).Offset(1).Row)).PasteSpecial xlPasteValues

End Sub

If you are really pasting the data to the static row then you can change the last line to

GD.Range("A12155").PasteSpecial xlPasteValues

VBA - Copy & Paste from onesheet to another using range function

1 Answers