• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

Excel macro to create hundreds of workbooks with names & date from one master file, based on a template

I have an Excel workbook called Master.xlsx.


The first row contains column headers.


The first cell in each row, starting at A2 through to A1200, contains a number, like 12345678, which is to be the new filename.


Columns B through AZ contain data I want to be written into each new workbook.


The end result is to be 1200 new files with names generated from column A each with row 2 popultaed with data from Master.xlsx.


The new files are all to be based on a template with formatting called MasterTemplate.xlsx which already has the first row populated with column headers the same as Master.xlsx.


I would like all the files to be created in folder D:\GeneratedWorkbooks


Thanks in advance!
0
snooflehammer
Asked:
snooflehammer
  • 5
  • 2
1 Solution
 
Robert SchuttSoftware EngineerCommented:
Try this:
Const C_TEMPLATE = "MasterTemplate.xlsx"
Const C_OUTPUT_PATH = "D:\GeneratedWorkbooks\"

Sub GenerateWorkbooks()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Dim rw As Integer
    Dim wbt As Workbook
    With ActiveSheet
        Set wbt = Workbooks.Open(C_TEMPLATE, , True, , , , , , , , , , False)
        For rw = 2 To .UsedRange.Rows.Count
            wbt.Sheets(1).Rows(2).Clear
            .Range(.Cells(rw, 2), .Cells(rw, 52)).Copy wbt.Sheets(1).Cells(2, 1)
            wbt.SaveAs C_OUTPUT_PATH & .Cells(rw, 1) & ".xlsx", , , , , , , , False
        Next
        wbt.Close
        Set wbt = Nothing
    End With
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Open in new window

If you want the copied cells to start at column B then change the end of line 13 to:
.Cells(2, 2)

Open in new window

Master.xlsm
0
 
snooflehammerAuthor Commented:
Where is this macro expecting to find MasterTemplate.xlsx ?
0
 
snooflehammerAuthor Commented:
Ignore previous. This crashes with runtime error 424 Object Required on the line "For rw = 2 To .UsedRange.Rows.Count"
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Robert SchuttSoftware EngineerCommented:
Did you try the attached file or did you put the macro in your existing file?

If the latter, how/where did you implement it (I had just put it under 'ThisWorkbook'), the only reason I can think of is ActiveSheet being undefined?

Maybe you can replace that with a direct reference to the sheet where your data is, maybe just Sheet1?
0
 
snooflehammerAuthor Commented:
I put the macro code in personal.xlsx.

I didn't notice the attached file. I just tried it and it returns a pop-up dialog "400" with no other info.

Sorry for being inept, but I don't know much about coding Excel Macros.

I've got the two workbooks, master.xlsx & mastertemplate.xlsx in D:\GeneratedWorkbooks

I changed the macro code to point to the mastertemplate.xlsx file thus:
Const C_TEMPLATE = "D:\GeneratedWorkbooks\MasterTemplate.xlsx"

I suspect the issue may be the location of master.xlsx. I have tried it with that file in  MY Docs, which I think is the correct place to no avail.

How to proceed?
0
 
snooflehammerAuthor Commented:
Scratch previous message. I opened Master.xlsx & ran the macro from there & it vomited new workbooks in a most satisfactory manner.

Top job!
0
 
snooflehammerAuthor Commented:
A well thought out answer that totally addressed my issue. Thank you very much
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now