Another excel / macro help request...

  xphile 16:54 28 Mar 2007
Locked

have this macro to filter items cleared since last monday:-
Sub test()
Dim Last_Monday As Date, LastRow As Long
Last_Monday = Mon_Start(Date)
Rows("5:5").Select
Selection.AutoFilter
Selection.AutoFilter Field:=8, Criteria1:=">=" & CLng(Last_Monday)
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A5:K" & LastRow).PrintOut Copies:=1, Collate:=True
Selection.AutoFilter
Range("A1").Select
End Sub

Is it possible to amend the above to make it print items cleared last week (Mon-Sun) ?

The reason for this is, that if I don't get around to running the above until following Monday, it will only print me ones cleared that day. (I think?)

  VoG II 17:21 28 Mar 2007

Sub test2()
Dim Last_Monday As Date, LastRow As Long
Last_Monday = Mon_Start(Date)
If Last_Monday = Date Then Last_Monday = Last_Monday - 7
MsgBox "Processing records cleared since Monday " & Last_Monday
Rows("5:5").Select
Selection.AutoFilter
Selection.AutoFilter Field:=8, Criteria1:=">=" & CLng(Last_Monday)
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A5:K" & LastRow).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

  xphile 13:07 29 Mar 2007

'Processing records cleared since Monday 26/3/07'. Then when I click Ok button that is there it prints records same as previous macro, cleared since Monday 26/3/07.

  VoG II 13:10 29 Mar 2007

That is what it should do. I thought that if you ran it on a Monday that you wanted to go back to the previous Monday.

  xphile 13:33 29 Mar 2007

If I run macro this week it will print what was cleared w/c 19/3/07 to w/e 25/3/07 & If I run it next week it will print cleared 26/3/07 to 1/4/07 etc.
Or have I got the wrong end of the stick ? If I run it actually on a Monday itself it will do previous Monday to Sunday?

  VoG II 13:36 29 Mar 2007

If you run it on any day it will clear everything back to the previous Monday. With the previous code, if you ran it on a Monday it would only clear to that Monday (i.e. one day only). Does this make sense?

  xphile 15:43 29 Mar 2007

If I run it on any day this week it only prints cleared items that were cleared Mon-Sun last week & nothing that's been cleared from Monday this week.If this is possible.

I need to get my hands on a Macro/VBA book don't I & get swotting.lol. Think I'll have a mooch down waterstones. I always find learning easier from a book than on the net. Thank you for all your help it is very much appreciated.

  VoG II 16:52 29 Mar 2007

If you record a macro to do that, what code do you get?

  xphile 17:52 29 Mar 2007

I would just set custom filter as in macro below, but is it possible for macro to automate this to run any time any week & get the previous Mon-Sun data only ?

Sub test3()
'
' test3 Macro
' Macro recorded 29/03/2007 by Craig
'

'
Rows("5:5").Select
Selection.AutoFilter
Selection.AutoFilter Field:=8, Criteria1:=">18/03/2007", Operator:=xlAnd _
, Criteria2:="<26/03/2007"
Range("A30:K1196").Select
Selection.PrintOut Copies:=1, Collate:=True
Range("A1").Select
Selection.AutoFilter
End Sub

  VoG II 18:16 29 Mar 2007

Sub test4()
Dim Last_Monday As Date, Last_Sunday As Date, LastRow As Long
'Get the previous Monday
Last_Monday = Mon_Start(Date)

Last_Sunday = Last_Monday - 8 ' Sunday 1 week before

MsgBox "Processing records cleared between Sunday " & Last_Sunday & " Monday " & Last_Monday
Rows("5:5").Select
Selection.AutoFilter
Selection.AutoFilter Field:=8, Criteria1:=">=" & CLng(Last_Sunday), Operator:=xlAnd, Criteria2:="<" & CLng(Last_Monday)
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A5:K" & LastRow).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

  xphile 17:04 01 Apr 2007

I have made 2 glaring omissions from the macro help request. The whole point of me being able to print off these lists was to include figures at the end.

1. To count the number of items cleared at bottom of printout at end of any column but K. Item names are in column , so maybe at end of this is preferable.

2. At end of column K sum total of column K. Column K is number of days taken to clear work item. So this would give me sum total of days to clear for all items cleared last week.

Is it possible to incorporate the above ? The figures could go above the column titles if easier than at the end ??? I have no idea.

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 ?