Create new files from a template named from list of cells

[technewsguy]

I have a template file named blankdas.xlsx. I want to create multiple copies/files of the template and have them named from a list in column A.
Name of 1st file would be content of A1, Name of 2nd file would be content of A2, .......

Thanks

[iamthetomkat]

Hello!

I'm new here, and while I've never done what you're looking to do, I did some research and found a couple things that may help you. You'll need to bump the file names down to start in cell A2, and relabel cell A1 as "FILENAMES" for both solutions to collaboratively work... but I hope this helps anyway. It sounds like you'd need to use VBA to accomplish the task, though.

I'm not certain, but in the "CODE" response below, you'd probably change the file name listed as "\Master.xlsx" to be your "\blankdas.xlsx" in order to make it work.

"Issue (from another forum): I need a macro to copy a file in a folder and make multiple copies of it in that very folder."

Response 1: CODE to create copy of files and rename them

Sub SaveMasterAs()
Dim wb As Workbook
Dim rNames As Range, c As Range, r As Range

'Current file's list of names and ids on sheet1.
Set rNames = Worksheets("Sheet1").Range("A2", Worksheets("Sheet1").Range("A2").End(xlDown))
'Path and name to master workbook to open for copy, saveas.
Set wb = Workbooks.Open(ThisWorkbook.Path & "\Master.xlsx")

For Each c In rNames
With wb
.Worksheets("Sheet1").Range("A1").Value = c.Offset(, 1).Value 'ID
'Path and name for copied workbook
.SaveAs Filename:=ThisWorkbook.Path & "\slave\" & c.Value & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End With
Set wb = ActiveWorkbook
Next c
wb.Close
End Sub

Response 2: Step-by-step to use a macro to create copy of files and rename them

Step 1 : Select the file you want to make copies of.
Step 2 : Select the destination folder / auto select it based on the folder of the file in Step 1.
Step 3 : List all the filenames in Column A starting from Cell A2 (Header Cell : FILENAMES)
Step 3 : Run Macro that will pick up the filenames listed in a column starting from Cell A2
and create multiple copies of that file with the names in Column A.

This macro should loop till the end of list in Column A is reached.

[Logit]

.
Another approach :

Option Explicit
Sub DupDel()

Columns("A:A").Select
ActiveSheet.Range("$A:$B").RemoveDuplicates Columns:=1, Header:=xlYes

'Uncomment next line and include RemoveBlankCells macro to delete all blank cells only in Col A
RemoveBlankCells

End Sub

Sub RemoveBlankCells()
'PURPOSE: Deletes single cells that are blank located inside a designated range
'SOURCE: www.TheSpreadsheetGuru.com

Dim rng As Range

'Store blank cells inside a variable
  On Error GoTo NoBlanksFound
    Set rng = Range("A:A").SpecialCells(xlCellTypeBlanks)
  On Error GoTo 0

'Delete blank cells and shift upward
  rng.Rows.Delete Shift:=xlShiftUp
MoreAndMoreSheets
Exit Sub

'ERROR HANLDER
NoBlanksFound:
    MsgBox "No Blank cells were found"

End Sub

Sub MoreAndMoreSheets()

    Dim ListSh As Worksheet, BaseSh As Worksheet
    Dim NewSh As Worksheet
    Dim ListOfNames As Range, LRow As Long, Cell As Range

    With ThisWorkbook
        Set ListSh = .Sheets("List") '--Qualify our sheets.
        Set BaseSh = .Sheets("Template")
    End With

    LRow = ListSh.Cells(Rows.Count, "A").End(xlUp).Row '--Get last row of list.
    Set ListOfNames = ListSh.Range("A2:A" & LRow) '--Qualify our list.

    With Application
        .ScreenUpdating = False '--Turn off flicker.
        .Calculation = xlCalculationManual '--Turn off calculations.
    End With

    For Each Cell In ListOfNames '--For every name in list...
        BaseSh.Copy After:=Sheets(Sheets.Count) '--Copy Template sheet.
        Set NewSh = ActiveSheet '--Let's name it NewSh.
        With NewSh
            On Error GoTo messageinfo '--In case of errors.
            .Name = Cell.Value '--Set the sheet's name to that of our current name in list.
            GoTo LetUsContinue '--Skip to the LetUsContinue block.

LetUsContinue:
            On Error GoTo 0 '--Turn off error handling.
            .Range("C1") = Cell.Value '--Change C1 to the name of current sheet.
            .Calculate '--Calculate page.
            .Cells.Copy '--Change all cells...
            .Cells.PasteSpecial xlPasteValues '--... to values.
        End With
    Next Cell

    With Application
        .Calculation = xlCalculationAutomatic '--Return to automatic calculation.
        .ScreenUpdating = True '--Return to proper state.
        
    End With
Exit Sub
    

messageinfo: '--In case of duplicate names...
MsgBox ("Duplicate name exists")
Err.Clear

On Error GoTo 0
End Sub

[technewsguy]

Logit,

Thanks so much for the info. This actually worked, but not what I need.
This creates TABS that named from the list.
I need to create a new Excel file containing the template info and each one
named from the A2 list.


Thanks