Saving excel sheet using inputbox to select date range.

tomsov

New Member
Joined
Mar 31, 2017
Messages
18
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 :)
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
I'm guessing this line of code errors:
VBA Code:
Lastrowa = newBk.Cells(Rows.Count, "F").End(xlUp).Row + 1

Change it to:
VBA Code:
Lastrowa = newBk.ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row + 1
 

tomsov

New Member
Joined
Mar 31, 2017
Messages
18
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

New Member
Joined
Mar 31, 2017
Messages
18
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Glad it's sorted & thanks for letting me know.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,936
Messages
5,656,024
Members
418,265
Latest member
ferdinandvs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top