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
]

 

[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

Can anyone help Please. Thank you.

 

[Fluff]

Try

   With Worksheets("TGT")
      If sheetdate >= StartDate And sheetdate <= enddate Then
         .Range(.Cells(i, 1), .Cells(1, 14)).Copy Destination:=wb.Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1)
      End If
   End With

 

[dlmoore99]

Fluff, I entered code you provided where I thought it should go and now receive Runtime error 9 subscript out of range for lastrow = Worksheets("TGT").UsedRange.Rows.Count

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
[COLOR=#FF0000]lastrow = Worksheets("TGT").UsedRange.Rows.Count[/COLOR]
For i = 2 To lastrow
    sheetdate = Cells(i, 1)
   With Worksheets("TGT")
      If sheetdate >= startdate And sheetdate <= enddate Then
         .Range(.Cells(i, 1), .Cells(1, 14)).Copy Destination:=wb.Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1)
      End If
   End With
Next i
End Sub

 

[Fluff]

Do you have a sheet called TGT?

 

[dlmoore99]

yes

 

[Fluff]

Check you don't have any trailing/leading spaces.

 

[dlmoore99]

I can't find any. Did I place your code in the correct place?

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)
[COLOR=#FF0000]        With Worksheets("TGT")
            If sheetdate >= startdate And sheetdate <= enddate Then
            .Range(.Cells(i, 1), .Cells(1, 14)).Copy Destination:=wb.Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1)
            End If[/COLOR]
        End With
Next i
End Sub

 

[Fluff]

yes, you've got it in the right place, but if you're getting an error on this line

lastrow = Worksheets("TGT").UsedRange.Rows.Count

It's not getting to the new part.
The error message that you got normally means that it can't find a sheet called TGT.
Select the TGT sheet & run this, what does it say?

Sub chk()
MsgBox "|" & ActiveSheet.Name & "|"
End Sub

 

[dlmoore99]

msgbox with |TGT|