VBA help in excel getting a macro to call itself

M

mayaicar

New Member
Joined
Sep 1, 2021
Messages
8
Office Version
  1. 2019
Platform
  1. Windows
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!


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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
AFAIK, the only way you could run a macro on a closed workbook would be to use Task Scheduler, which comes with Windows. I think you'll also need a script that will do the actual work of calling the macro. Here's but one example (author states that pc has to be on, but I'm not sure if that is true).
www.thespreadsheetguru.com

How To Automatically Run Excel VBA Macros Daily

Learn how to utilize a few programs you already have to create automation rules that will automatically open up your Excel files, run VBA code, save the changes, and then close the file. These automations can even be setup to run while you sleep!
www.thespreadsheetguru.com www.thespreadsheetguru.com
 
Upvote 0
Solution
Micron said:
author states that pc has to be on, but I'm not sure if that is true
Click to expand...
Yes, I think you will want the Scheduler job that runs on a computer that is always on.
I typically run these sorts of things from our servers, which are always on.
 
Upvote 0
The computer for sure has to be switched on. Hibernation wouldn't be a problem though, provided a specific task in WTS awakes the computer before any other task is performed.
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Trying to learn how to loop code
Replies
1
Views
233
mumps
mumps
C
  • Question Question
VBA-Photos to be imported from Sharepoint Site rather than local drive
Replies
1
Views
546
Micron
M
S
  • Solved
VBA: Am I double-calling a Sub given nested If statements? Or other inefficiencies?
Replies
8
Views
440
dreid1011
dreid1011
M
  • Solved
VBA Date Issue!!!
Replies
4
Views
671
mayaicar
M
T
  • Solved
VBA - Make same formulas into a single one
Replies
5
Views
429
Michael M
Michael M

Forum statistics

Threads
1,223,888
Messages
6,175,208
Members
452,618
Latest member
Tam84

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top