VBA - Apply Macros to 100 workbooks without needing to open workbooks individually to apply Macros

[robbertly]

Hello,

I am looking for any assistance in relation to Excel Macros and VBA

I am currently using the following on my Excel Workbooks (which were kindly provided by people on MrExcel)


Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+z
'
Dim ws As Worksheet

For Each ws In Sheets
ws.Activate
Range("C1:Z10").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next ws
End Sub


Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+y
'
Dim ws As Worksheet

For Each ws In Sheets
ws.Activate
Range("C14:Z2060").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next ws
End Sub


Sub ProtectWB_Protect_All_Sheets()
Dim ws As Worksheet

ActiveWorkbook.Unprotect

For Each ws In Worksheets
ws.Protect
Next

ActiveWorkbook.Protect
End Sub


These Macros turn RandBetween numbers into fixed / non-changing 'values', instead of having changing output of the RandBetween formula every-time I open / use the workbook / worksheet. The last one locks the workbook.

My situation is as follows:

• I have a workbook with 24 worksheets
• I need to create 100 individual workbooks, with each workbook having 24 sheets, most likely using the original as a master to make copies from
• Each worksheet, 24 per workbook, has the same structure and layout - with the affected RandBetween content in cells C1 to Z10, and C14 to Z2060

My questions are:

• Firstly, is it possible to combine these three Macros into one Macro? Or even the first two into one Macro?
• Secondly, and more importantly, is it possible to create / design a VBA to apply the above macros to all 100 workbooks (affecting each of the 24 worksheets in each of the 100 workbooks simultaneously), without physically needing to open each workbook individually and apply those macros to each individual workbook?

Any help will be gratefully appreciated.

Robbert

 

[Jeffrey Mahoney]

Simple answer. A macro cannot add VBA code to any workbook.

You could create a template with the macros. Open each WB, copy the data to the template, and save as a new version.

 

[ranman256]

put the macro in your PERSONAL WORKBOOK.
this way, the macro is not in any 1 workbook, yet can open all workbooks.

on any sheets, click record macro,
on STORE MACRO IN: choose personal workbook
ok
click on any cell
stop macro.
save.

now you can put the code in the Personal Workbook.

 

[Micron]

I'll add that it is inefficient to select ranges in order to copy and again to paste. You will likely find this to be slow over 100 wb x 24 sheets if that is your intent. Just use the copy command in a way that is suitable to the requirement (e.g. as copy what to paste where)
Worksheets("Sheet1").AutoFilter.Range.Offset(0, 0).Copy Sheets("MailBody").Range("A1")

A macro cannot add VBA code to any workbook.

Disagree. It is called exstensibility but hardly worth the effort IMO; especially if you can simply copy a template workbook as 100 other identical workbooks if you intend to run code from them separately. If these are to be handed out to 99 other people, putting code in your personal file will be no good to anyone else.

My answer to both questions is yes. However, if it fits the requirement it would be easier to copy a template x number of times. Then the same code would be in each wb.

 

[robbertly]

Hi All,

Many thanks for posting on this, much appreciated.

Overall, based on the feedback, I think using a 'master' version and copying it with the macros included might be the most practical option.

Although a bit labour intensive, it might provide the best results.

Again, thanks for following up with me on this.

Robbert

 

[Micron]

Code should be able to produce 100 wb's in about 30 seconds or less (assuming no network traffic issues). They would not be able to be in the same folder and have the same name regardless of which way you do it, but that could be worked in.