I have created a macro for each month and would like it to run on workday 4 of the following month until the next month workday 4. Below I have created the code to call each monthly macro given a date range, however when I run the date_range sub it doesn't seem to account for the date range. I have included the July macro it should call if the month is August workday 4 for visibility (the macro is very similar for each month so only included this one macro).
The code can be found below. If you could please give any aid as the date range and format is correct to my best knowledge.
The code can be found below. If you could please give any aid as the date range and format is correct to my best knowledge.
VBA Code:
Sub Date_Range()
Dim StartDate As Date
Dim EndDate As Date
Dim result As String
StartDate = Now()
'dateformat is m/dd/yyyy
If StartDate >= #8/5/2021# And EndDate < #9/5/2021# Then
Call July_drop_down
ElseIf StartDate >= #9/6/2021# And EndDate < #10/5/2021# Then
Call August_drop_down
ElseIf StartDate >= #10/6/2021# And EndDate < #11/3/2021# Then
Call September_drop_down
ElseIf StartDate >= #11/4/2021# And EndDate < #12/5/2021# Then
Call October_drop_down
ElseIf StartDate >= #12/6/2021# And EndDate < #1/5/2022# Then
Call November_drop_down
ElseIf StartDate >= #1/6/2022# And EndDate < #2/3/2022# Then
Call December_drop_down
ElseIf StartDate >= #2/4/2022# And EndDate < #3/3/2022# Then
Call January_drop_down
ElseIf StartDate >= #3/4/2022# And EndDate < #4/5/2022# Then
Call February_drop_down
ElseIf StartDate >= #4/6/2022# And EndDate < #5/4/2022# Then
Call March_drop_down
ElseIf StartDate >= #5/5/2022# And EndDate < #6/5/2022# Then
Call April_drop_down
ElseIf StartDate >= #6/6/2022# And EndDate < #7/5/2022# Then
Call May_drop_down
ElseIf StartDate >= #7/6/2022# And EndDate < #8/3/2022# Then
Call June_drop_down
Else
MsgBox ("out of date")
End If
End Sub
Sub July_drop_down()
'
' July_drop_down Macro
' All tabs within the file will choose July actuals. Designed to run on WD4 August. Month = 7 Quarter = 3
'
' Keyboard Shortcut: Ctrl+Shift+l
'
Range("K51").Select
Sheets("Abs. $MM - Spot FX").Select
Range("K9").Value = "ALL"
Range("K10").Value = "ALL"
Range("K11").Value = 7
Range("K12").Value = 2021
Range("K13").Value = "Actuals"
Range("K15").Value = "ALL"
Range("K16").Value = 3
Range("K17").Value = "ALL"
Range("K18").Value = 2021
Range("K19").Value = "Actuals"
Range("K21").Value = "ALL"
Range("K22").Value = "ALL"
Range("K23").Value = 7
Range("K24").Value = 2021
Range("K25").Value = "Guidance Q3"
Range("K27").Value = "Spot FX"
Range("K40").Select
Sheets("Abs. $MM - Constant FX").Select
Range("K9").Value = "ALL"
Range("K10").Value = "ALL"
Range("K11").Value = 7
Range("K12").Value = 2021
Range("K13").Value = "Actuals"
Range("K15").Value = "ALL"
Range("K16").Value = 3
Range("K17").Value = "ALL"
Range("K18").Value = 2021
Range("K19").Value = "Actuals"
Range("K21").Value = "ALL"
Range("K22").Value = "ALL"
Range("K23").Value = 7
Range("K24").Value = 2021
Range("K25").Value = "Guidance Q3"
Range("K27").Value = "Constant FX"
Range("K51").Select
Sheets("% GMS").Select
Range("K9").Value = "ALL"
Range("K10").Value = "ALL"
Range("K11").Value = 7
Range("K12").Value = 2021
Range("K13").Value = "Actuals"
Range("K15").Value = "ALL"
Range("K16").Value = 3
Range("K17").Value = "ALL"
Range("K18").Value = 2021
Range("K19").Value = "Actuals"
Range("K21").Value = "ALL"
Range("K22").Value = "ALL"
Range("K23").Value = 7
Range("K24").Value = 2021
Range("K25").Value = "Guidance Q3"
Range("K27").Value = "Constant FX"
Range("K54").Select
Sheets("$ Per Order").Select
Range("K9").Value = "ALL"
Range("K10").Value = "ALL"
Range("K11").Value = 7
Range("K12").Value = 2021
Range("K13").Value = "Actuals"
Range("K15").Value = "ALL"
Range("K16").Value = 3
Range("K17").Value = "ALL"
Range("K18").Value = 2021
Range("K19").Value = "Actuals"
Range("K21").Value = "ALL"
Range("K22").Value = "ALL"
Range("K23").Value = 7
Range("K24").Value = 2021
Range("K25").Value = "Guidance Q3"
Range("K27").Value = "Constant FX"
Range("K60").Select
Sheets("$ Per Unit").Select
Range("K9").Value = "ALL"
Range("K10").Value = "ALL"
Range("K11").Value = 7
Range("K12").Value = 2021
Range("K13").Value = "Actuals"
Range("K15").Value = "ALL"
Range("K16").Value = 3
Range("K17").Value = "ALL"
Range("K18").Value = 2021
Range("K19").Value = "Actuals"
Range("K21").Value = "ALL"
Range("K22").Value = "ALL"
Range("K23").Value = 7
Range("K24").Value = 2021
Range("K25").Value = "Guidance Q3"
Range("K27").Value = "Constant FX"
Range("K40").Select
Sheets("$ Per Unit | ProcP focus").Select
Range("K9").Value = "ALL"
Range("K10").Value = "ALL"
Range("K11").Value = 7
Range("K12").Value = 2021
Range("K13").Value = "Actuals"
Range("K15").Value = "ALL"
Range("K16").Value = 3
Range("K17").Value = "ALL"
Range("K18").Value = 2021
Range("K19").Value = "Actuals"
Range("K21").Value = "ALL"
Range("K22").Value = "ALL"
Range("K23").Value = 7
Range("K24").Value = 2021
Range("K25").Value = "Guidance Q3"
Range("K27").Value = "Constant FX"
Range("M44").Select
Sheets("Conventional OPPU view").Select
Range("S1").Value = "Retail"
Range("S2").Value = "INT6"
Range("S3").Value = "2021"
Range("S4").Value = "ALL"
Range("S5").Value = "3"
Range("S6").Value = "YTD"
Range("S7").Value = "Actuals"
Range("T1").Value = "Retail"
Range("T2").Value = "UK"
Range("T3").Value = "2021"
Range("T4").Value = "ALL"
Range("T5").Value = "3"
Range("T6").Value = "YTD"
Range("T7").Value = "Actuals"
Range("U1").Value = "Retail"
Range("U2").Value = "DE"
Range("U3").Value = "2021"
Range("U4").Value = "ALL"
Range("U5").Value = "3"
Range("U6").Value = "YTD"
Range("U7").Value = "Actuals"
Range("V1").Value = "Retail"
Range("V2").Value = "FR"
Range("V3").Value = "2021"
Range("V4").Value = "ALL"
Range("V5").Value = "3"
Range("V6").Value = "YTD"
Range("V7").Value = "Actuals"
Range("W1").Value = "Retail"
Range("W2").Value = "IT"
Range("W3").Value = "2021"
Range("W4").Value = "ALL"
Range("W5").Value = "3"
Range("W6").Value = "YTD"
Range("W7").Value = "Actuals"
Range("X1").Value = "Retail"
Range("X2").Value = "ES"
Range("X3").Value = "2021"
Range("X4").Value = "ALL"
Range("X5").Value = "3"
Range("X6").Value = "YTD"
Range("X7").Value = "Actuals"
Range("Y1").Value = "Retail"
Range("Y2").Value = "JP"
Range("Y3").Value = "2021"
Range("Y4").Value = "ALL"
Range("Y5").Value = "3"
Range("Y6").Value = "YTD"
Range("Y7").Value = "Actuals"
Range("AA1").Value = "Retail"
Range("AA2").Value = "INT6"
Range("AA3").Value = "2021"
Range("AA4").Value = "ALL"
Range("AA5").Value = "3"
Range("AA6").Value = "YTD"
Range("AA7").Value = "Actuals"
Range("AA8").Value = "% GMS"
Range("AB1").Value = "Retail"
Range("AB2").Value = "UK"
Range("AB3").Value = "2021"
Range("AB4").Value = "ALL"
Range("AB5").Value = "3"
Range("AB6").Value = "YTD"
Range("AB7").Value = "Actuals"
Range("AB8").Value = "% GMS"
Range("AC1").Value = "Retail"
Range("AC2").Value = "DE"
Range("AC3").Value = "2021"
Range("AC4").Value = "ALL"
Range("AC5").Value = "3"
Range("AC6").Value = "YTD"
Range("AC7").Value = "Actuals"
Range("AC8").Value = "% GMS"
Range("AD1").Value = "Retail"
Range("AD2").Value = "FR"
Range("AD3").Value = "2021"
Range("AD4").Value = "ALL"
Range("AD5").Value = "3"
Range("AD6").Value = "YTD"
Range("AD7").Value = "Actuals"
Range("AD8").Value = "% GMS"
Range("AE1").Value = "Retail"
Range("AE2").Value = "IT"
Range("AE3").Value = "2021"
Range("AE4").Value = "ALL"
Range("AE5").Value = "3"
Range("AE6").Value = "YTD"
Range("AE7").Value = "Actuals"
Range("AE8").Value = "% GMS"
Range("AF1").Value = "Retail"
Range("AF2").Value = "ES"
Range("AF3").Value = "2021"
Range("AF4").Value = "ALL"
Range("AF5").Value = "3"
Range("AF6").Value = "YTD"
Range("AF7").Value = "Actuals"
Range("AF8").Value = "% GMS"
Range("AG1").Value = "Retail"
Range("AG2").Value = "JP"
Range("AG3").Value = "2021"
Range("AG4").Value = "ALL"
Range("AG5").Value = "3"
Range("AG6").Value = "YTD"
Range("AG7").Value = "Actuals"
Range("AG8").Value = "% GMS"
Range("AI1").Value = "Retail"
Range("AI2").Value = "INT6"
Range("AI3").Value = "2021"
Range("AI4").Value = "ALL"
Range("AI5").Value = "3"
Range("AI6").Value = "YTD"
Range("AI7").Value = "Actuals"
Range("AI8").Value = "$ per unit"
Range("AJ1").Value = "Retail"
Range("AJ2").Value = "UK"
Range("AJ3").Value = "2021"
Range("AJ4").Value = "ALL"
Range("AJ5").Value = "3"
Range("AJ6").Value = "YTD"
Range("AJ7").Value = "Actuals"
Range("AJ8").Value = "$ per unit"
Range("AK1").Value = "Retail"
Range("AK2").Value = "DE"
Range("AK3").Value = "2021"
Range("AK4").Value = "ALL"
Range("AK5").Value = "3"
Range("AK6").Value = "YTD"
Range("AK7").Value = "Actuals"
Range("AK8").Value = "$ per unit"
Range("AL1").Value = "Retail"
Range("AL2").Value = "FR"
Range("AL3").Value = "2021"
Range("AL4").Value = "ALL"
Range("AL5").Value = "3"
Range("AL6").Value = "YTD"
Range("AL7").Value = "Actuals"
Range("AL8").Value = "$ per unit"
Range("AM1").Value = "Retail"
Range("AM2").Value = "IT"
Range("AM3").Value = "2021"
Range("AM4").Value = "ALL"
Range("AM5").Value = "3"
Range("AM6").Value = "YTD"
Range("AM7").Value = "Actuals"
Range("AM8").Value = "$ per unit"
Range("AN1").Value = "Retail"
Range("AN2").Value = "ES"
Range("AN3").Value = "2021"
Range("AN4").Value = "ALL"
Range("AN5").Value = "3"
Range("AN6").Value = "YTD"
Range("AN7").Value = "Actuals"
Range("AN8").Value = "$ per unit"
Range("AO1").Value = "Retail"
Range("AO2").Value = "JP"
Range("AO3").Value = "2021"
Range("AO4").Value = "ALL"
Range("AO5").Value = "3"
Range("AO6").Value = "YTD"
Range("AO7").Value = "Actuals"
Range("AO8").Value = "$ per unit"
Range("AL10").Select
Sheets("Top & Bottom Line").Select
Range("K9").Value = "ALL"
Range("K10").Value = 3
Range("K11").Value = "ALL"
Range("K12").Value = 2021
Range("K13").Value = "Actuals"
Range("K15").Value = "ALL"
Range("K16").Value = 3
Range("K17").Value = "ALL"
Range("K18").Value = 2020
Range("K19").Value = "Actuals"
Range("K21").Value = "ALL"
Range("K22").Value = 3
Range("K23").Value = "ALL"
Range("K24").Value = 2021
Range("K25").Value = "Guidance Q3"
Range("K27").Value = "Constant FX"
'Call timer
ActiveWorkbook.Save
End Sub
Last edited by a moderator: