Using Input Boxes to Create a Report for Date Range

windwardmi

Board Regular
Joined
Oct 18, 2009
Messages
138
Hello All,

I am trying to pull rows of info from a specific sheet. The rows I want to pull are for a certain date range in column 1. Creating an employees timesheet for the week.

I started with a recorded Macro and tried to modify the code but got stumped here. The macro I recorded used the filter method for dates between start and end dates.

The following is the code from the original Macro I have created. Any and all comments welcomed. Thank you.

Code:
Sub TimeSheet()
'
' TimeSheet Macro
'
' Keyboard Shortcut: Ctrl+t
'
Dim rightsheet As String
Dim startdate As Date
Dim enddate As Date
 
rightsheet = Application.InputBox("Employee Last Name?")
 
Sheets(rightsheet).Select
Selection.AutoFilter
startdate = Application.InputBox("Start Date?")
enddate = Application.InputBox("End Date?")
ActiveSheet.Range("$A$1:$P$2").AutoFilter Field:=1, Criteria1:="startdate", _
    Operator:=xlAnd, Criteria2:="enddate"
    
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
End Sub
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Re: USING INPUT BOXES TO CREATE A REPoRT FOR DATE RANGE

So what problem are you actually getting? Other than your autofilter being applied to rows 1 and 2 only?
 
Upvote 0
Re: USING INPUT BOXES TO CREATE A REPoRT FOR DATE RANGE

Hi,
try changing this part of your code:

Code:
ActiveSheet.Range("$A$1:$P$2").AutoFilter Field:=1, Criteria1:="startdate", _
    Operator:=xlAnd, Criteria2:="enddate"


to this:

Code:
ActiveSheet.Range("$A$1").CurrentRegion.AutoFilter Field:=1, Criteria1:=">=" & CLng(StartDate), _
    Operator:=xlAnd, Criteria2:="<=" & CLng(EndDate)

and see if this does what you want.

You may also want to consider managing the Cancel Button on the inputbox & invalid date entries.

Dave.
 
Last edited:
Upvote 0
Re: USING INPUT BOXES TO CREATE A REPoRT FOR DATE RANGE

I thank you dmt32 and baitmaster for replying. Inputting dmt32's advice and a few tweaks I came up with the following working code. This gets me to a basic report of employee time sheet data for a date range. I'm now looking to progress and or finalize the code to produce a clearer report. See below and tell me if I need to start a new thread for each goal and can I add it to the present macro.


Goals:
1. Auto input employees "name" 20pt font and next line "begindate and enddate" into header center section.
2. Below data collected have a Row totaling multiple columns indicating total number of hours for the time period requested.
3. Add to Offfice 365 SharePoint in order to gather dailies from all employees
3. Not yet thought of.

Again thank you.

Code:
Sub TimeSheet()
'
' TimeSheet Macro
'
' Keyboard Shortcut: Ctrl+t
'
Dim rightsheet As String
Dim StartDate As Date, EndDate As Date
 
rightsheet = Application.InputBox("Employee Last Name?")
 
Sheets(rightsheet).Select
Selection.AutoFilter
StartDate = Application.InputBox("Start Date?")
EndDate = Application.InputBox("End Date?")
ActiveSheet.Range("$A$1").CurrentRegion.AutoFilter Field:=1, Criteria1:=">=" & CLng(StartDate), _
    Operator:=xlAnd, Criteria2:="<=" & CLng(EndDate)
'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
'IgnorePrintAreas:=False
End Sub
 
Upvote 0
Re: USING INPUT BOXES TO CREATE A REPoRT FOR DATE RANGE

I thank you dmt32 and baitmaster for replying. Inputting dmt32's advice and a few tweaks I came up with the following working code. This gets me to a basic report of employee time sheet data for a date range. I'm now looking to progress and or finalize the code to produce a clearer report. See below and tell me if I need to start a new thread for each goal and can I add it to the present macro.


Goals:
1. Auto input employees "name" 20pt font and next line "begindate and enddate" into header center section.
2. Below data collected have a Row totaling multiple columns indicating total number of hours for the time period requested.
3. Add to Offfice 365 SharePoint in order to gather dailies from all employees
3. Not yet thought of.

Going nuts in trying to do goal one. Present code line does not do much. I try to increase font etc and get error messages due to bad code writing. Any help to do Goal 1 above.

Code:
ActiveSheet.PageSetup.CenterHeader = ActiveSheet.Name
 
Upvote 0
Re: USING INPUT BOXES TO CREATE A REPoRT FOR DATE RANGE

Try

Code:
ActiveSheet.PageSetup.CenterHeader = "&20" & rightsheet & Chr(10) & "&10" & "From: " & StartDate & " To: " & EndDate

Dave
 
Upvote 0
Re: USING INPUT BOXES TO CREATE A REPoRT FOR DATE RANGE

Try

Code:
ActiveSheet.PageSetup.CenterHeader = "&20" & rightsheet & Chr(10) & "&10" & "From: " & StartDate & " To: " & EndDate

Dave

What resulted in Header was this

From: 12:00:00 AM To: 12:00:00 AM

Not

"ActiveSheet Name"
From: "StartDate to EndDate"


 
Upvote 0
Re: USING INPUT BOXES TO CREATE A REPoRT FOR DATE RANGE

I got the dates to appear. I need to see how I can make header two lines. Stand by I will seek online.
 
Upvote 0
Re: USING INPUT BOXES TO CREATE A REPoRT FOR DATE RANGE

I'm getting closer to the Header working correctly.

I need to get 1st line in header to use a larger font then 2nd line and also decrease line space between both. Here is the code. I hope I'm going in the right direction and all can benefit.

Code:
Sub TimeSheet()
'
' TimeSheet Macro
'
' Keyboard Shortcut: Ctrl+t
'

Dim WS As Worksheet
Dim rightsheet As String
Dim StartDate As Date, EndDate As Date

StartDate = Application.InputBox("Start Date?")
EndDate = Application.InputBox("End Date?")

ActiveSheet.PageSetup.CenterHeader = "&12" & rightsheet & Chr(10) & ActiveSheet.Name & Chr(13) & rightsheet & Chr(10) & (StartDate) & " To " & (EndDate)
rightsheet = Application.InputBox("Employee Last Name?")
 
Sheets(rightsheet).Select
Selection.AutoFilter
ActiveSheet.Range("$A$1").CurrentRegion.AutoFilter Field:=1, Criteria1:=">=" & CLng(StartDate), _
    Operator:=xlAnd, Criteria2:="<=" & CLng(EndDate)
'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
'IgnorePrintAreas:=False
End Sub
 
Upvote 0
Re: USING INPUT BOXES TO CREATE A REPoRT FOR DATE RANGE

I believe I am segwaying from the original topic and will start a new thread.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

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
Back
Top