Excel Macro - Create Multiple Workbooks [Closed]

Report
Posts
2
Registration date
Friday December 15, 2017
Status
Member
Last seen
December 15, 2017
-
Posts
2
Registration date
Friday December 15, 2017
Status
Member
Last seen
December 15, 2017
-
Hello!

I have a 4 tab workbook I need to create multiple copies of (one for each employee to use throughout the entire coming year).
I have my Data tab, Manager tab, Rep tab, and Precap tab on said workbook that appear in that order.
On the Manager tab is a cell that will have the Employee's name in it (B5:D5. Its a merged cell).
I need a macro to create a copy of this workbook for every single name starting in A2 on the Data tab,
put the same name in that cell on the Manager tab in B5:D5,
and relabel the workbook and name it whatever is in the cell next to the name on the Data tab (B2).
It needs to keep going until it comes to the end of Column A on the Data tab.
The copies also need to save as a plain old Excel Workbook.
The save path is just going to be to my desktop (C:\Users\sblackbourne\Desktop)

If it cannot be done then I will have to create 200+ workbooks manually so ANY help would be greatly appreciated!
If you have any questions let me know please!

Thanks!

1 reply


OK so start by recording a macro of what you wish for the first thing to do. then create the variable to make it scale.

Post some code and we can help out!
Posts
2
Registration date
Friday December 15, 2017
Status
Member
Last seen
December 15, 2017

So this is a rough draft of what I need it to do:

Sheets("MANAGER").Select
Range("B5:D5").Select
ActiveCell.FormulaR1C1 = "=Data!R[-3]C[-1]"
Sheets("Data").Select
ActiveWindow.SelectedSheets.Visible = False
ChDir "C:\Users\sblackbourne\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\sblackbourne\Desktop\TESTtestTEST.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub


This only creates one copy of the workbook.
The ("=Data!R[-3]C[-1]") string is referring to A2 on my Data tab and I need it to create a workbook for every cell in Column A.
The workbook name (TESTtestTEST ) would need to be automatically selected from the cell to the right (B2) on the Data tab.

I just do not know enough abut macros on how to get it to look to a corresponding cell to label the workbook nor how to get it to create so many copies without writing a section of code for each workbook

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!