Automatically create and name workbooks from a template and list

sfauerbach

New Member
Joined
Mar 9, 2007
Messages
5
Hello,
I have a workbook with several worksheets: Data, Calculations, Codes, and Report. The Report draws information from the Data and Calculations pages based on a code I insert into cell A1 on the Calculations worksheet. The Codes worksheet lists the 300 codes I need to create customized reports for.

I would like to automate the process so that I can create new workbooks for each code. I'd like the new file names to be the same as the code. The new workbooks should only contain the customized Report for the code it's named for (not the other worksheets like Data, Calculations, Codes).

Please help! I have limited experience with Macros but have used VBA to write a macro once before.

Thanks,
SMF
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
If you are simply trying to rename the each worksheet according to what is in cell A1 Try this code:

Code:
Sub RenameSheets()

For i = 1 To Sheets.Count
If Worksheets(i).Range("A1").Value <> "" Then
Sheets(i).Name = Worksheets(i).Range("A1").Value
End If
Next


End Sub
Do you remember how to add a module to Excel's VBA editor? (see below if not)

Just press "alt" F11, then Insert from the menu bar select "module" and paste the above code. Close the window by hitting, "alt" F11 again to return to your spreadsheet and then alt F8 to view your macros and select the one title "RenameSheets" and hit run. See if that works for you.

Important note: Remember to save before you run a macro because you can undo it.
Let me know if thats what you were looking for.
 
Upvote 0

Forum statistics

Threads
1,222,028
Messages
6,163,482
Members
451,838
Latest member
DonSlayer

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