acro to input date and print based on date range

February 16, 2012 at 06:35:37
Specs: Windows XP
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).

Date Day Activity Income
02/01/2012 Monday Work (8) £48.00 02/01/2012
HMOT £5.00 02/01/2012 Peter Taylor 134024855
HMOT £5.00 02/01/2012 Susan Taylor 134023422
HMOT £5.00 02/01/2012 Steve Davy 134027374
04/01/2012 Wednesday Work (8) £48.00 04/01/2012
HMOT £5.00 04/01/2012 Tom Thompson 134027410
HMOT £5.00 04/01/2012 John Ackroyd 134025131
HMOT £5.00 04/01/2012 Aftab Ilahi 134022546

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.

Please feel free if that doesn't make sense to ask questions.

Thanks in advance.

See More: acro to input date and print based on date range

Report •

February 16, 2012 at 06:37:57
Below is the code I have for a macro which I can't seem to get to work right.


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
      .AutoFilterMode = False
    End With
    Set rngTarget = Nothing
    Set rngDate = Nothing
    Set wksCurr = Nothing
  End If
  End If
  Exit Sub
  Select Case Err
  Case Else
    MsgBox "Error " & CStr(Err) & ": " & Err.Description
    Resume Proc_Exit
  End Select
  Exit Sub
End Sub

Report •

February 16, 2012 at 07:22:14
[code] and [/code] does not work in this forum. Please click on the following line for the instructions on how to post code in this forum. Thanks!

Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

Ask Question