Excel VBA Command Button to open new workbook and copypaste rows

[dlmoore99]

I have a userform that contains two date pickers and a command button. I would like to copy all rows (14 columns) that fall between the two date pickers from my worksheet("TGT") to a new workbook worksheet when I click the cmd button. I keep getting a debugger at the "Range" code line. Thank you in advance. This is what I have so far:

[Private Sub cmdCSV_Click()
Dim lastrow As Long, i As Long, erow As Long
Dim sheetdate As Date, startdate As Date, enddate As Date
Dim wb As Workbook
Set wb = Workbooks.Add

startdate = DTPicker1.Value
enddate = DTPicker2.Value
lastrow = Worksheets("TGT").UsedRange.Rows.Count
For i = 2 To lastrow
sheetdate = Cells(i, 1)
If sheetdate >= startdate And sheetdate <= enddate Then
erow = Worksheets("TGT").UsedRange.Rows.Count + 1
Range(Cells(i, 1), Cells(1, 14)).Copy Destination:=wb.Worksheets("sheet1").Cells(erow, 1)

End If
Next i
End Sub
]

 

[Fluff]

It would help if I read your code properly, try

Private Sub cmdCSV_Click()
   Dim lastrow As Long, i As Long, erow As Long
   Dim sheetdate As Date, startdate As Date, enddate As Date
   Dim wb As Workbook
   Dim ws As Worksheet
   
   Set ws = Worksheets("TGT")
   Set wb = Workbooks.Add
   
   startdate = DTPicker1.Value
   enddate = DTPicker2.Value
   lastrow = ws.UsedRange.Rows.Count
   For i = 2 To lastrow
      sheetdate = Cells(i, 1)
      If sheetdate >= startdate And sheetdate <= enddate Then
         ws.Range(ws.Cells(i, 1), ws.Cells(1, 14)).Copy Destination:=wb.Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1)
      End If
   Next i
End Sub

The code was looking for a sheet called TGT in the active workbook, but as the code had created a new workbook, it was looking in the new workbook.

 

[dlmoore99]

now getting error Run-time error "424" Object required at the

startdate = DTpicker.value

and when I run the code the DTpickers do not appear

 

[Fluff]

Try putting the word Me in front like

   startdate = Me.DTPicker1.Value
   enddate = Me.DTPicker2.Value

If that doesn't sort it, move those 2 lines before the Workbook.Add

 

[dlmoore99]

Ok now it will create a new workbook/sheet but it is blank, no data is copied from the "TGT" worksheet to the new worksheet and debugger points back to: Set ws = Worksheets("TGT")

 

[Fluff]

If the debugger points to that line, how did it create a new workbook?
Also what was the error message?

 

[dlmoore99]

unknown how it created a new blank workbook/sheet, msgbox Run-time error '9': Subscript out of range

 

[Fluff]

If the UserForm in the same workbook as sheet TGT try

Set ws = ThisWorkbook.Worksheets("TGT")

 

[dlmoore99]

Ok, now it just creates a new workbook, but no data is copied from worksheet "TGT" for the selected date range or any other range

 

[Fluff]

Missed a bit

 sheetdate = [COLOR=#ff0000]ws.[/COLOR]Cells(i, 1)

 

[dlmoore99]

new workbook opens, no data copied, get error, Run-time error'13': Type mismatch at

sheetdate = ws.Cells(i, 1)