I have a data set in Excel. Starting at column C, it has continuous data from C2 to a variable number that changes everytime it is used For every row of continuous data in column D, I want to select column C-J’s rows as well. I’m trying to select this data to create a new excel with that data.

The code I have gathered around to build what i was hoping to create doesnt seem to be working.

Dim lastVal As Range, sht As Worksheet

Set sht = Sheets("sheet1")

Set lastVal = sht.Columns(4).Find("*", sht.Cells(1, 48), xlValues, _
                                xlPart, xlByColumns, xlPrevious)

Debug.Print lastVal.Address

sht.Range("C2", lastVal).Resize(, 48).Select
selection.copy

Set Objexcel = CreateObject("Excel.application")
objexcel.visible = True
objexcel.cells(1, 1).paste