Why do dollar signs appear on my desktop?

Weekday function in macro

• QUESTION: , This should be a simple question for you. I use macros to save files automatically depending on the date or open files with yesterdays date. such as: Dim A As String A = "G:My Documents" %26 Format(Date - 1, "m-d-yy") %26 ".xlsx" ActiveWorkbook.SaveAs Filename:=A End Sub Or a if FileExists Function If FileExists(A) then Workbooks.Open Filename:=A End If Is it possible to use the Weekday or Workday Function in the same way, to exclude Saturday and Sunday? It's just a pain when I try to use the macro on a monday and it will try to open Sunday's workbook. When I need it to open Friday's Workbook. Format(Weekday - 1, "m-d-yy") Format(Workday - 1, "m-d-yy") Excel won't accept these. Thank you for your help. ANSWER: Nick, Dim A As String, l as long Dim inc as Long l = Weekday(Date, vbMonday) If l = 1 Then inc = -3 Else inc = -1 End If A = "G:My Documents" %26 Format(Date + inc, "m-d-yy") %26 ".xlsx" ActiveWorkbook.SaveAs Filename:=A ---------- FOLLOW-UP ---------- QUESTION: Is it possible to also include holidays with this macro? The range of cells of all the listed holidays is on the Worksheet("Running Total") $A$29:$A$35. Thats an absolute cell range, if the dollar signs dont appear when I submit this follow-up question. Dim sh1 As WorkSheet Dim r As Range Set sh1 = Worksheets("Running Total") Set r = sh1.Range("A29:A35") Can I subtract "r" from the macro, such as: l = Weekday(Date, vbMonday) - Date(r) or will I need to list each holiday?

• Answer:

  Nick Dim sh1 As WorkSheet Dim r As Range, inc as long Set sh1 = Worksheets("Running Total") Set r = sh1.Range("A29:A35") inc = 0 Do inc = inc - 1 if application.Countif(r,date + inc) = 0 and _ weekday(date + inc, vbMonday) Loop A = "G:My Documents" %26 Format(Date + inc, "m-d-yy") %26 ".xlsx" ActiveWorkbook.SaveAs Filename:=A for testing I created this macro. Note that when run today, it makes Wednesday 26 May and Thursday 27 May as holidays so the date calculated should be Tuesday May 25. Sub abbc() Dim sh1 As Worksheet Dim r As Range, inc As Long 'Set sh1 = Worksheets("Running Total") Set sh1 = ActiveSheet Set r = sh1.Range("A29:A35") r(1) = Date - 1 r(2) = Date - 2 inc = 0 Do inc = inc - 1 If Application.CountIf(r, Date + inc) = 0 And _ Weekday(Date + inc, vbMonday) Loop Debug.Print Format(Date + inc, "mmm dd, yyyy dddd") 'A = "G:\My Documents\" & Format(Date + inc, "m-d-yy") & ".xlsx" 'ActiveWorkbook.SaveAs Filename:=A End Sub the macro produced May 25, 2010 Tuesday just as a demo. If I make Monday through Thursday as holidays (by entering those dates in the holiday range) and run the macro I get May 21, 2010 Friday (both are the correct answers).