Save excel file as specified titles in different workbook Closed

Question

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

TrowaD · Answer

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

Anna · Answer

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

TrowaD · Answer

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

Save excel file as specified titles in different workbook

3 responses

Similar discussions

Subscribe To Our Newsletter!