Saving excel sheet using inputbox to select date range.

[tomsov]

Hello, I've been working on a complicated workbook but have ran into a problem that I can't seem to solve. I need to output a list of invoices to an external application (it can be imported as an .xls file but must be in a stand alone workbook on Sheet 1 for this to work). I have so far managed to build a worksheet with all the data arranged as I need, and this will build up in the background behind the 'user control worksheet' whereby invoices that are posted to the system will also be added to a worksheet called 'Sage Output' (which is the sheet I want to select and output user selected dates to a new stand alone workbook. I hope this makes sense!

Here's where I'm up to, sorry about the messy code but I have hacked and changed it a lot to no avail! The dates are held in column 'F' and the user is asked a 'from' and 'to' date range to output.

'Create New Workbook
'
Sub PromptUserForInputDates()
Dim newBk As Workbook
Set newBk = Workbooks.Add

ThisWorkbook.Activate
'On Error GoTo IncorrectAnswer
Dim i As Long
Dim ans As Date
Dim anss As Date
Dim Lastrow As Long
Dim Lastrowa As Long
ans = InputBox("Start Date Is")
anss = InputBox("End Date Is")
Lastrow = ThisWorkbook.Sheets("Sage Output").Cells(Rows.Count, "F").End(xlUp).Row
Lastrowa = newBk.Cells(Rows.Count, "F").End(xlUp).Row + 1
For i = 1 To Lastrow
If Cells(i, "F").Value >= ans And Cells(i, "F").Value <= anss Then
Rows(i).Copy Destination:=newBk.Rows(Lastrowa)
Lastrowa = Lastrowa + 1
End If
Next

newBk.Range("F1:F" & Lastrowa).NumberFormat = "dd/mm/yyyy"
'Application.ScreenUpdating = True
Exit Sub
'IncorrectAnswer:
'MsgBox "The date format is incorrect, use only full date reference dd/mm/yyyy"
End Sub

This gives runtime error 438, object doesn't support this property or method.

Help is very much appreciated! Thanks

 

[GWteB]

I'm guessing this line of code errors:

Lastrowa = newBk.Cells(Rows.Count, "F").End(xlUp).Row + 1

Change it to:

Lastrowa = newBk.ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row + 1

 

[tomsov]

Brilliant, that sorted that line out - but the error has now moved to :

Rows(i).Copy Destination:=newBk.Rows(Lastrowa)

Error 438, object doesn't support this property or method. I'm not sure of the command that refers to 'the other workbook' which I presume must be placed before the newBk.Rows?

Thanks again!

 

[tomsov]

Ah ha!!! Thank you so much - You've headed me in the right direction! Here's the code that now works brilliantly (within the sub):

Dim i As Long
Dim ans As Date
Dim anss As Date
Dim Lastrow As Long
Dim Lastrowa As Long
ans = InputBox("Start Date Is")
anss = InputBox("End Date Is")
Lastrow = ThisWorkbook.Sheets("Sage Output").Cells(Rows.Count, "F").End(xlUp).Row
Lastrowa = newBk.ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row + 1
For i = 1 To Lastrow
If Cells(i, "F").Value >= ans And Cells(i, "F").Value <= anss Then
Rows(i).Copy Destination:=newBk.ActiveSheet.Rows(Lastrowa)
Lastrowa = Lastrowa + 1
End If
Next

newBk.ActiveSheet.Range("F1:F" & Lastrowa).NumberFormat = "dd/mm/yyyy"

THANK YOU GWteB, You are a star!!

 

[GWteB]

Glad it's sorted & thanks for letting me know.