Hi all,
I wish to create a macro that when i click a button i can input what month i wish to print out data for. I have 7 columns (A-G).
| 27/01/2012 |
Friday |
Work (8) |
£48.00 |
27/01/2012 |
|
|
|
|
HMOT |
£5.00 |
27/01/2012 |
Steph Burton |
134022382 |
|
|
HMOT |
£5.00 |
27/01/2012 |
Nigel Pollock |
134027488 |
| 30/01/2012 |
Monday |
Work (8) |
£48.00 |
30/01/2012 |
|
|
|
|
HMOT |
£5.00 |
30/01/2012 |
Hannah loft |
134026425 |
|
|
HMOT |
£5.00 |
30/01/2012 |
Lisa-Marie Reyman |
134027425 |
|
|
HMOT |
£5.00 |
30/01/2012 |
Katherine Wood |
134027151 |
| 31/01/2012 |
Tuesday |
Work (8) |
£48.00 |
31/01/2012 |
|
|
|
|
HMOT |
£5.00 |
31/01/2012 |
Christine Emmott |
134027507 |
|
|
PT (off) |
£15.00 |
31/01/2012 |
Nick Kitchen |
Sports Massage |
| 01/02/2012 |
Wednesday |
Work (8) |
£48.00 |
01/02/2012 |
|
|
|
|
HMOT |
£5.00 |
01/02/2012 |
Tom Slack |
134027412 |
|
|
HMOT |
£5.00 |
01/02/2012 |
Matt Beaumont |
134027490 |
Above is an example of the sheet.
In column A we have the date, B we have the day, C we have the activity, D we have the income, E is a cell with the previous month date in to track data for the month, F is a name and G is a number.
I wish to be able to print all of january for example, rows A-G by clicking a button and entering something like 'january' or '01/12'. This will then select all of the rows with data in based on the date range in column A and print the selection.
Below is the code I have for a macro which i cant seem to get to work right.
Thanks.
Public Sub PrintMonth()
On Error GoTo Proc_Error
Dim wksCurr As Worksheet
Dim rngTarget As Range
Dim strInput As String
Dim datInput As Date
Dim datStart As Date
Dim datEnd As Date
strInput = InputBox("input start date for month", "Enter date")
If strInput > "" Then
datInput = CDate(strInput)
If datInput > 0 Then
Set wksCurr = ActiveSheet
Set rngTarget = wksCurr.Columns(4)
datStart = WorksheetFunction.EoMonth(datInput, -1) + 1
datEnd = WorksheetFunction.EoMonth(datInput, 0) + 1
With wksCurr
If .AutoFilterMode Then
.AutoFilterMode = False
End If
.UsedRange.AutoFilter Field:=rngTarget.Column, Criteria1:=">=" & datStart, _
Operator:=xlAnd, Criteria2:="<" & datEnd
.PrintOut
.AutoFilterMode = False
End With
Set rngTarget = Nothing
Set rngDate = Nothing
Set wksCurr = Nothing
End If
End If
Proc_Exit:
Exit Sub
Proc_Error:
Select Case Err
Case Else
MsgBox "Error " & CStr(Err) & ": " & Err.Description
Resume Proc_Exit
End Select
Exit Sub
End Sub
|