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]

Try

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

If that still gives an error check that your dates are proper dates, rather than text that looks like a date

 

[dlmoore99]

still get the same results with that code line: new workbook opens, no data copied, get error, Run-time error'13': Type mismatch, I verified the properties of the date picker

 

[Fluff]

You need to check the value in the cell when you get the error.

 

[dlmoore99]

What cell, the dates are coming from the DT Picker in VBA Tools and not in a cell but more like a textbox. everything is located on a userform, the DTPicker and cmdCSV button. Now on the worksheet("TGT") the dates are located in column B, is that possibly where the issue lies? I still need all columns for A(1) - N(14) to be copied.

 

[Fluff]

This line

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

is looking at a cell in col A & then assigning the value of that cell to the variable sheetdate, which is declared as Date.
When the code fails check what the value of i is & then check what is in that cell. So if i is 11 check the value of A11

 

[dlmoore99]

so the value of A11 isn't a date, dates are located in column B. how do I adjust the code to look in column B on the TGT worksheet

 

[Fluff]

Change the 1 to a 2

 

[dlmoore99]

I tried that and still get a Type mismatch error at

sheetdate = ws.Cells(i, 2).Value

 

[Fluff]

You need to check the value in the cell when the code fails, it's probably not a real date.

 

[dlmoore99]

I ensured that the dates in column B on TGT worksheet are formatted as dates. Not sure what other cells there is to check.