Excel VBA Pasting to Range with Variable Row

June 23, 2023

I am trying to paste into a range with a variable row. The row is based on the last row + 1. I am getting a debug error when I try to run the code. It should be B2:T2, B3:T3, etc. based on the last row plus one.

I used the standard copy paste with range/cells and the variable for the row. My other copy paste’s in the application work just fine.

Dim lr As Integer, cr As Integer
      Dim r As Range
      
      lr = wb_main.Worksheets("Data").Range("A" & Rows.Count).End(xlUp).Row
      cr = lr + 1
      Set r = wb_main.Worksheets("Data").Range("A" & lr)
      r.Offset(1).Value = r.Value + 1
      
      wb_main.Worksheets("Main").Range("E3:W3").Copy wb_main.Worksheets("Data").Range(Cells(cr, 2), Cells(cr, 20))
      wb_main.Worksheets("Main").Range("E3:W3").Delete

>Solution :

You have to qualify both .Cells in the same way as you did .Range

One option is:

Dim wks As Worksheet
Set wks = wb_main.Worksheets("Data")
wb_main.Worksheets("Main").Range("E3:W3").Copy wks.Range(wks.Cells(cr, 2), wks.Cells(cr, 20))