excel macro to print filtered items...

  xphile 08:43 25 Mar 2007
Locked

I have an excel workbook that has tabs for each month with work items in each month,with date received & date cleared columns. At the end of each week I print off items cleared since last Monday by auto filtering & doing a custom filter in date cleared column to show date = to or > than last Monday - E.g 19/3/07. Then highlight all filtered list & print it. I am struggling with doing a macro button to do this as when I get to subsequent weeks it will still filter from 19/3/07. How can I get it to filter from only the last Monday from date I am running the macro & filter just those. I.e. When I run it next Friday it will filter ones cleared from & including 26/3/07. Is it possible ? & can any of you excel brainiacs help me ?

  VoG II 10:35 25 Mar 2007

This function

Function Mon_Start(sdate As Date) As Date
Mon_Start = sdate - (sdate - 2) Mod 7
End Function

will return the date of the previous Monday. So for example running

Sub test()
MsgBox Mon_Start(Date)
End Sub

today returns 19/03/2007.

  xphile 12:32 25 Mar 2007

Where do I put the above Function ? Do I click tools/macros/record macro ? Or type into somewhere else ? Does the above function filter my list to show cleared since last monday ? Do I then have to record part of macro to print etc ?

  VoG II 12:36 25 Mar 2007

Can you post the macro that you have recorded please?

  VoG II 21:32 25 Mar 2007

To post the macro, press ALT + F11 to bring up the Visual Basic Editor. In the left hand pane click on the + to the left of Modules. Double click on Module1 (or the name of the module that you saved your macro to). Copy the code in the right hand pane and post it here.

  xphile 08:55 26 Mar 2007

that's what I want to do. At the moment I auto filter the ones cleared since last monday(E.g. =to or > 19/3/07) & then print them. I want macro to do this. I don't know how to record macro to do this from the previous Monday to date I run the macro. I can only do macro to filter as I have been doing. But that would keep the date static wouldn't it? I want it to work so that each week end it just prints ones cleared that week & not previous weeks. I don't have a clue about vba. Any help greatly appreciated.

  VoG II 09:53 26 Mar 2007

If you record a macro of you doing this manually then we should be able to adapt it to automatically do this referencing the previous Monday. To record a macro in Excel 2003 and earlier Tools > Macro > Record New Macro.

  xphile 12:13 26 Mar 2007

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 26/03/2007 by Craig
'

'
Rows("5:5").Select
Selection.AutoFilter
Selection.AutoFilter Field:=8, Criteria1:="19/03/07"
Range("A5:K897").Select
Selection.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter
Range("A1").Select
End Sub

Not convinced I've even done that right but seems to print off ok.

  xphile 12:14 26 Mar 2007

= to or > 19/3/07. The above just prints 19/3/07.

  VoG II 12:26 26 Mar 2007

OK well try this. I haven't been able to test it as I don't have your data to try it on but it compiles OK.

Macros are not reversible so I suggest that you make a copy of your workbook to try this on.

Unfilter the data.

ALT+F11 to open the Visual Basic Editor, Insert > Module.

Copy and paste in the following code:

Sub test()
Dim Last_Monday As Date
Last_Monday = Mon_Start(Date)
Rows("5:5").Select
Selection.AutoFilter
Selection.AutoFilter Field:=8, Criteria1:=Last_Monday
Range("A5:K897").Select
Selection.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter
Range("A1").Select
End Sub

Function Mon_Start(sdate As Date) As Date
Mon_Start = sdate - (sdate - 2) Mod 7
End Function


Close the VBE and select the sheet with your data. Tools > Macro > Macros, click on 'test' then click the Run button.

  VoG II 12:28 26 Mar 2007

Ah, missed your other post. You probably need

Selection.AutoFilter Field:=8, Criteria1:= ">" & Last_Monday

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman

iPad Pro 10.5in (2017) review

Comment booster votre iPhone ?