Create Multiple Workbooks With Template At Once

Dak903 - Jan 12, 2023 at 09:46 AM
 Dak903 - Jan 16, 2023 at 02:23 PM

Hello, I'm being tasked with copying a single workbook with a special layout in it and copying it about 90 times with the file name corresponding to a rental property we own. I have an excel file with the naming I need each book to have (ex., Website Audit - Wayne Manor 2023). Is there a way to do this so I don't have to CTRL+C, CTRL+V rename? 

I also have to open each workbook and update the website link and property name in the top left cell and sheet name, but what I care about most is just creating the files to start. I'm kind of a novice and worried if I try to do too much it will confuse me. 

Thank you. 


Windows / Chrome 108.0.0.0

2 replies

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Jan 12, 2023 at 12:10 PM

Hi Dak,

Good thing you came to this site, as that is a simple task for Excel VBA!

Paste the following code in the workbook with all the names:
 

Sub RunMe()
Dim oFSO As Object

Set oFSO = CreateObject("Scripting.FileSystemObject")

For Each cell In Range("A2:A5")
    Call oFSO.CopyFile("J:\Kioskea\Test Map\Template.xlsx", "J:\Kioskea\Test Map\" & cell.Value & ".xlsx")
Next cell
End Sub

First let's adjust the code, where you have to replace the path and the file name of the template in the code above (J:\Kioskea\Test Map\Template.xlsx) before the comma on code line 7.
Next change the destiantion path of where all the copied and renamed workbbooks needs to go (J:\Kioskea\Test Map\) after the comma on code line 7.
Also change the range where the file names are located (Range("A2:A5") on code line 6.

To implement the code hit Alt+F11, to open another window, go to the top menu, click on Insert and click on Module. Now paste the code in the big white field. You can close this window now. Back at Excel, hit Alt+F8 and double click RunMe to run the code.

Best regards,
Trowa


1

Wow. Thank you!! That was a lot easier than I thought it would be. Thank you for the explanation as well.

Is it possible to write in code for it to add a name in another column into the new workbook and rename the sheet? 

Example: Inserts name into A1 from a list and then renames the sheet to what it puts in a1?

Or insert information from another column into the new workbook? 

So, after it copies the template and renames, it then puts in the website address from a list into the new workbook into, say, Cell F1?

I'm really trying to learn!

0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Jan 16, 2023 at 11:47 AM

Hi Dak,

Feast your eyes on this code:
 

Sub RunMe()
Dim oFSO As Object
Dim MyBook As Workbook
Dim SheetName, HyperName As String

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set MyBook = ActiveWorkbook

With MyBook.Sheets("Sheet1")
    For Each cell In .Range("A2:A5")
        SheetName = .Cells(cell.Row, "B")
        HyperName = .Cells(cell.Row, "C")
        Call oFSO.CopyFile("J:\Kioskea\Test Map\Template.xlsx", "J:\Kioskea\Test Map\" & cell.Value & ".xlsx")
        Workbooks.Open "J:\Kioskea\Test Map\" & cell.Value & ".xlsx"
        ActiveSheet.Name = SheetName
        Range("A1").Value = SheetName
        ActiveSheet.Hyperlinks.Add Anchor:=Range("F1"), Address:=HyperName, TextToDisplay:=HyperName
        Workbooks(cell.Value & ".xlsx").Close savechanges:=True
    Next cell
End With
End Sub

Range A2:A5 is used for the filename.
Range B2:B5 is used for the sheet name and also cell A1.
Range C2:C5 is used for the hyperlinks placed in cell F1.

Glad to be able to help you out! Feel free to ask how stuff works or to add to the code.

Best regards,
Trowa


1

You. are. AMAZING!! Thank you. I will look this over and let you know if I have any questions. I love learning this kind of thing so I'm excited to dive into it!!

1