Save excel file as specified titles in different workbook

- - Latest reply: TrowaD
Posts
2488
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 20, 2019
- Jun 11, 2019 at 11:36 AM
Hello Trowa and team,

I just saw you solved a task for another user, who wanted to recreate a file with specified names in the list.

I have a similar task, but my names ( to be titles) are in different files. So I need the macro to be in the file that has names list in column A and when run, to recreate another excel file( which is a simple form) by the names in the file where the macros is> In other words I need to individualize the form by copying and titling it with names stored in different file.
And I need the final resulting files do not have anything to do with macros, while opening they should not ask to enable.
This was your code for different user, can you please modify it for my needs?

Sub CreateWBs() Dim lRow, x As Integer Dim wbName As String lRow = Range("A" & Rows.Count).End(xlUp).Row x = 1 Dox = x + 1wbName = Range("A" & x).Value & "_" & Range("B" & x).ValueActiveWorkbook.SaveAs Filename:="C:\Documents\" & wbName & ".xls" Loop Until x = lRow End Sub


Thank you so much and appreciate your help a lot!!!
Have a great day
Anna
See more 

3 replies

Posts
2488
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 20, 2019
354
0
Thank you
Hi Anna,

It seems I didn't know how to post codes back then :). I always like to recap the question so there is no confusion as to what the code does.

So you have a Master workbook, which has workbook names in column A with a header. Now you want to copy the Master workbook and rename it according to the names mentioned in column A. The copied and renamed workbooks should not contain the code.

For that give the following code a try, but only after replacing the file path:
Sub RunMe()
Dim x As Integer

Application.DisplayAlerts = False

x = 2

Do
    ActiveWorkbook.SaveAs Filename:="C:\Documents\" & Range("A" & x).Value & ".xlsx", FileFormat:=xlOpenXMLWorkbook
    x = x + 1
Loop Until Range("A" & x).Value = vbNullString

ActiveWorkbook.Close

Application.DisplayAlerts = True

End Sub


Best regards,
Trowa

Respond to TrowaD
0
Thank you
Hi Trowa,

Thank you for your fast response, I appreciate it a lot.
However, I just want to make one correction to the scenario - my master file that has names in column A and macros is in one file, but the copy the macros should be is of another file. So the macros should tell: take the names that are in the home file column A, and go to this file ( separate file - that is just a simple form/ template) > make as many copies of that file as the names are here and title them by names.
That is the task, and you did most part of that. THANK YOU!!!

Will be looking forward to hear from you!
Anna
Respond to Anna
Posts
2488
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 20, 2019
354
0
Thank you
Hi Anna,

My bad, I missed the separate template file part.

Here you go:
Sub RunMe()
Dim x As Integer, wbName As String

x = 2

Do
    wbName = Range("A" & x).Value
    Workbooks.Open Filename:="C:\Documents\Template File.xlsx"
    With ActiveWorkbook
        .SaveAs Filename:="C:\Documents\" & wbName & ".xlsx"
        .Close
    End With
    x = x + 1
Loop Until Range("A" & x).Value = vbNullString

End Sub


Best regards,
Trowa
Respond to TrowaD