I have a data set in Excel. Starting at column C, it has continuous data from C2 to a number that changes every time 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 workbook with that data.
When I run:
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
I get:
run-time error '13': type mismatch
on the Set LastVal line.
Something like this:
Sub Tester()
Dim sht As Worksheet, wb As Workbook, shtNew As Worksheet
Set sht = ThisWorkbook.Sheets("sheet1") 'or activeworkbook
Set wb = Workbooks.Add() 'add new workbook
Set shtNew = wb.Worksheets(1)
'copy from Col C, using last row from Col D?
'not sure if that's really what you want though?
With sht.Range("C2:J" & sht.Cells(Rows.Count, "D").End(xlUp).Row)
Debug.Print "copying", .Address
'straight copy
.Copy shtNew.Range("A1")
'...or copy values only
shtNew.Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
End Sub
In place of sht.Cells(Rows.Count, "D") you can use any other column in which you don't have any blanks.