Run time 1004 Error - Defining last column in cell to copy range

[Eclektics]

Hi everyone,

I'm trying to use a count formula on my worksheet to tell VBA the range of columns I want to copy. This will change between different data sets. I've got the last column number on my "Home" sheet and the range to copy is on the "Calculation" sheet.

I'm not sure why the below isn't not working, I've gone through a few different threads on here to try and resolve it but to no avail. The macro stops on '.Range(Cells(2, ColNo), Cells(LRow, ColNo)).Copy'

Sub test_module()


Dim ColNo, LRow As Long


LRow = Sheets("Calculation").Cells(Sheets("Calculation").Rows.Count, "A").End(xlUp).Row


With Worksheets("Home")
    ColNo = .Range("K7").Value
End With


With Worksheets("Calculation")
    .Range(Cells(2, ColNo), Cells(LRow, ColNo)).Copy
End With


Sheets("Home").Range("P4").PasteSpecial xlPasteValues


End Sub

 

[Yongle]

Try replacing that line with

.Cells(2 , ColNo).Resize(LRow - 1).Copy