Microsoft Excel - Macro to print data in date range

Asked By tom on 16-Feb-12 03:06 PM

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


wally eye replied to tom on 16-Feb-12 04:57 PM
How about you post your worksheet?
tom replied to wally eye on 17-Feb-12 01:02 AM
You can see it in the post above.

ps. forum won't allow .xls
wally eye replied to tom on 17-Feb-12 09:17 AM
Yes, I wrote most of that code.  Zip up your spreadsheet and post the .zip.
tom replied to wally eye on 18-Feb-12 08:37 AM
I know :)

Thank you!

Money Tracker - Jan 12.zip
wally eye replied to tom on 19-Feb-12 10:16 PM
You can just change:
 
Set rngTarget = wksCurr.Columns(4)

to

Set rngTarget = wksCurr.Columns(5)

which is looking at Column E instead of Column D.  However, it looks like your formula is replicated down about 1000 rows, so when I filter on February 2012, it shows 943 rows.
tom replied to wally eye on 20-Feb-12 09:41 AM
That's used because I need to know if the date changes to wrk out how many days have been worked during the month.

Is it not possible to use column A which contains the date, or using the code in column E to calculate the print range and stop at the last row of data entered into column C??

ps. what did you type into the input box to get the data?

Thanks
wally eye replied to tom on 20-Feb-12 01:54 PM
OK, so I was being lazy earlier.  Yes, you can filter on your amount column as well.  Here, I've replaced all the code between "If datInput > 0 then" and "End If"

    Set wksCurr = ActiveSheet
    datStart = WorksheetFunction.EoMonth(datInput, -1) + 1
    datEnd = WorksheetFunction.EoMonth(datInput, 0) + 1
    With wksCurr
    If .AutoFilterMode Then
    .AutoFilterMode = False
    End If
    .UsedRange.AutoFilter Field:=5, Criteria1:=">=" & datStart, _
        Operator:=xlAnd, Criteria2:="<" & datEnd
    .UsedRange.AutoFilter Field:=4, Criteria1:=">0"
    .PrintOut
    .AutoFilterMode = False

It would be difficult to filter on your column A date column.

I hesitate to tell what I input into the date input, "2/1" because that is shorthand in the US for 2/1/2012 and might not be for the UK.  Regional settings will determine valid entries and how what you input will be converted into a date.  So, just put in what you think is a valid date and it should be interpreted at the datInput = CDate(strInput) line.
tom replied to wally eye on 20-Feb-12 02:54 PM
Thanks,

I get an error:
Object doesnt support this property or method.

Thanks
wally eye replied to tom on 20-Feb-12 09:47 PM
tom replied to wally eye on 21-Feb-12 02:44 AM
The ZIP file seems to be empty?

Heh, thanks.
wally eye replied to tom on 21-Feb-12 09:56 PM
I guess I missed a fairly important step!

Money Tracker - Jan 12.zip
tom replied to wally eye on 22-Feb-12 07:46 AM
Unfortunatley, that still doesn't work.

I am currently using office 2003 at work and office 2010 at home.I get different errors on both systems; I am going to try the new version of this on my computer once I get home and i'll post you the results.

The good news is, if it is a software issue, our system at work is due to be upgraded to 2010.

Thank you for your help and perceveriance (sp?) on this!
tom replied to wally eye on 24-Feb-12 07:08 AM
Hi,

Just tried this spreadsheet on my office 2010 system and it seems to work, however.. It only prints off one day (and always seems to be the same day).

Step in the right direction though! :)

What changes need to be made?
thanks,.
wally eye replied to tom on 27-Feb-12 11:12 AM
It seems to be working on my machine.  You might try putting a breakpoint on the line that reads:

.UsedRange.AutoFilter Field:=5, etc

Hover over datStart and datEnd to check that the dates are what you would expect, that is the first of the selected month and the first of the next month.

If they are, then press F8 twice to run the next two lines of code and apply the filter on the worksheet, then look at the worksheet filters to verify that they match what you want to see.

Let me know what comes out of this test...
tom replied to wally eye on 28-Feb-12 02:46 AM
The error occurs when it gets passed to :
datStart = WorksheetFunction.EoMonth(datInput, -1) + 1

at this point, datInput is the date i've put in (01/02/2012) but datStart is 00:00:00, then the error occurs.

Thanks
wally eye replied to tom on 28-Feb-12 06:30 PM

It sound like you need to load the Analysis ToolPak add-in.

HideHow?

  1. On the File menu, click Add-Ins.
  2. In the Add-Ins available list, select the Analysis ToolPak box, and then click OK.
  3. If necessary, follow the instructions in the setup program.

If that doesn't work, we can revert to DateValue, which is a bit tricky given regional differences:

datStart = DateValue(Year(datinput) & " " & Format(datinput, "mmm") & " 01")
datEnd = DateValue(Year(datinput) + Int(Month(datinput) / 12) & " " & Format(datStart + 31, "mmm") & " 01")