I'm very new to VBA and therefore this is probably a very simple question - I've just been stuck on it for a while now!
I have an excel workbook which requires certain values on each sheet to change each month (according to the month). I would like to code to perform the following things: i) run the macro even if excel isn't open, ii) for the macro to loop everyday (ideally early hours of the morning BST, but not crucial) throughout the month, iii) the macro to start running on work day 4 of each month, and finish on work day 3 of the following month, iv) to save the excel document with the updates the macro has performed.
Below is the code I currently have as a basis for August actuals (code is quite long but very simple!). Ideally I would like the code to perform the features above: run the macro everyday from 6th September 2021 to 5th October 2021 (both WD4s) and save the document. Thank you in advance!
I have an excel workbook which requires certain values on each sheet to change each month (according to the month). I would like to code to perform the following things: i) run the macro even if excel isn't open, ii) for the macro to loop everyday (ideally early hours of the morning BST, but not crucial) throughout the month, iii) the macro to start running on work day 4 of each month, and finish on work day 3 of the following month, iv) to save the excel document with the updates the macro has performed.
Below is the code I currently have as a basis for August actuals (code is quite long but very simple!). Ideally I would like the code to perform the features above: run the macro everyday from 6th September 2021 to 5th October 2021 (both WD4s) and save the document. Thank you in advance!
VBA Code:
Sub August_drop_down()
'
' August_drop_down Macro
' All tabs within the file will choose August actuals. Designed to run on WD4 September. Month = 8 Quarter = 3
'
' Keyboard Shortcut: Ctrl+g
'
Range("K51").Select
Sheets("Abs. $MM - Spot FX").Select
Range("K9").Value = "ALL"
Range("K10").Value = "ALL"
Range("K11").Value = 8
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 = 8
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 = 8
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 = 8
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 = 8
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 = 8
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 = 8
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 = 8
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 = 8
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 = 8
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 = 8
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 = 8
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"
End Sub