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]

Find out what the value of i is when the code fails, then, in a blank cell enter this =ISNUMBER(B6) replace 6 with the value of i.
What does the formula return?

 

[dlmoore99]

Ok, so as I continue to learn and understand VBA code, I was looking at the "For i = 2 To lastrow" line of code and something didn't look right. If I'm understanding this line correctly the "For i" is looking at the rows, the "= 2" is looking at which row to start looking at, and the "To lastrow" means look at all rows until a row is blank. If i understand this correctly then, I need to change my start row. I entered adjusted code as my data i want to start lookin at is in row four, all Previous are headers or blank,

For i = 4 To lastrow

After I changed code I ran the code and a new worksheet popped up like is suppose to, but I ended up with 11 tables, all tables have the same headers, first table has the first date found, 2nd table has first two rows that meet the date parameters, 3rd table has the first 3 rows that had met date parameters, 4th table has first 4 rows that met date parameters, all the way up to 11th table will all 11 dates that met date parameters. How do I fix this so that only one table will all 11 dates appears?

Here is the last code I used:

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 = ThisWorkbook.Worksheets("TGT")
    Set wb = Workbooks.Add
   
    startdate = Me.DTPicker1.Value
    enddate = Me.DTPicker2.Value
    lastrow = ws.UsedRange.Rows.Count
    For i = 4 To lastrow
        sheetdate = ws.Cells(i, 2).Value
        If sheetdate >= startdate And sheetdate <= enddate Then
        ws.Range(ws.Cells(i, 2), ws.Cells(1, 15)).Copy Destination:=wb.Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1)
        End If
    Next i
End Sub

 

[Fluff]

There's a typo on this line

ws.Range(ws.Cells(i, 2), ws.Cells([COLOR=#ff0000]i[/COLOR], 15)).Copy Destination:=wb.Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1)

It should be an i not a 1

 

[dlmoore99]

Ok, made that change and now lost the headers. Also what is needed to keep the same format of the current worksheet("TGT") in the new worksheet? Better yet, I'll look the formatting up in the forums.