Create a macro to run from a master workbook but program it to run on another workbook that gets overwritten daily and cannot store a macro.

abishop2022

New Member
Joined
Apr 11, 2022
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
I have a workbook that is updated daily (deleted and replaced with new workbook). I would like to be able to create a macro that runs on a schedule to format this new workbook after it is created. I looked into this and I started by creating a "MacroWorkbook.xlsm" and copying the sheet that I want to format from the new workbook over to the MacroWorkbook.xlsm and then created a macro with the specific formatting I need.
How do I manage to schedule this macro to run on the new workbook?
 
Sorry, I really cannot help you with .vbs script.
That is really out-of-scope for this question (you did not mention wanting to do anything like that until now, and VBS script in NOT the same as VBA).
Isn't that what I need to do to automate the macro?
 
Upvote 0

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
I just viewed my macro again and it seems to not have recorded where I opened the new workbook? See attached pic.
 

Attachments

  • Macro.PNG
    Macro.PNG
    47.5 KB · Views: 3
Upvote 0
Isn't that what I need to do to automate the macro?
There are different ways that it can be done.
I posted a link to a way to do it that does not require you to use VBS.
That link shows how you can automate VBA code to run upon opening the file.
If you want it to run at a certain day/time, you can just use Windows Scheduler to then open the file when you want.
 
Upvote 0
I just viewed my macro again and it seems to not have recorded where I opened the new workbook? See attached pic.
If you record yourself opening another Excel workbook, you should have a line of code that looks like this:
Rich (BB code):
    Workbooks.Open Filename:= "file_path_and_name"
where "file_path_and_name" is the full file path and name of the Excel file you opened.
 
Upvote 0
If you record yourself opening another Excel workbook, you should have a line of code that looks like this:
Rich (BB code):
    Workbooks.Open Filename:= "file_path_and_name"
where "file_path_and_name" is the full file path and name of the Excel file you opened.
Yes, that was there before and now it is gone? Where would I put that line? At the beginning of the script?
 
Upvote 0
Yes, that was there before and now it is gone?
Mabe you accidentally deleted it. VBA code does not automatically delete itself.

Where would I put that line? At the beginning of the script?
Wherever it makes sense to put it. If you aren't sure, sketch out the order of operations (flow) that you want your code to do, and just follow that plan (you need to have a plan for what you want to happen before you can program it!).
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

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