Automatically create and name workbooks from a template and list

[sfauerbach]

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

 

[Bryman]

If you are simply trying to rename the each worksheet according to what is in cell A1 Try this 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.