You are here:

Excel/creating multiple files

Advertisement


Question
QUESTION: Hi Tom

I have an excel file.  On a worksheet called "createfiles" I have a list of names in range A1:A40

Is it possible to create a macro that will do the following?:

Create an identical set of files that have been rename according to the names in the range A1:A40

So that the names "AN other" and "AN Other2" are in cells A1 and A2

When the VBA is run two new files will be created "AN Other" and AN Other2".

These files would be placed in T:\

Thanks in advance.


Chris M

ANSWER: Christopher,

it isn't clear - am I creating 40 files by copying the original file and using the names in the sheet createfiles in cells A1:A40

then what do you mean about when the vba is run two files are created with names AN other and AN other2.

sorry, but I don't understand what you are asking.

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Hi Tom

The amount of files created will depend on the number of names in the range A1:A40 (which can be found in worksheet "createfiles").  If there are only two name in the range then only two new workbooks would be created.

When the VBA is run two new workbooks would be created.  One would be named "AN Other" and the other "AN Other 2".

So if the name "AN Other" was written in cell A1 and "AN other 2" was written in cell A2.  Two new workbooks (identical to the original one) would be created: one named "AN Other" and the other named AN Other2"

The files would be saved at T:\

ANSWER: Christopher,

This should do what you describe:

Note:  this will copy the worksheets.  It won't copy any code that is not in the sheet modules.  It won't copy any type of sheet except worksheets.  If that isn't what you want, then post back.  Since I know only a little about what you want, I  made some assumptions.

Sub ABC()
Dim bk As Workbook, bk1 As Workbook
Dim sh As Worksheet
Dim r As Range

Set bk = ThisWorkbook
Set sh = bk.Worksheets("Createfiles")
Set r = sh.Range("A1", sh.Cells(41, "A").End(xlUp))
On Error GoTo ErrHandler
For Each cell In r
 sName = ""
 If Len(cell.Text) > 0 Then
   sName = cell.Text & ".xlsm"
   bk.Worksheets.Copy
   Set bk1 = ActiveWorkbook
   bk1.SaveAs Filename:="T:\" & sName, _
     FileFormat:=xlOpenXMLWorkbookMacroEnabled
   bk1.Close SaveChanges:=False
 End If
Next
Exit Sub
ErrHandler:
MsgBox "Error - problem with file: " & sName
Resume Next
End Sub



--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Hi tom

Thank you for your patience.  The misunderstanding is my fault.  This code is fine.  However, I would like the new workbooks to contain the code in the original workbook.  All of your assumptions were correct.

Is this something that can be achieved?

Thanks in advance

Chris

Answer
Christopher,

OK.  Try this:

Sub abcde()
Dim bk As Workbook, bk1 As Workbook
Dim sh As Worksheet
Dim r As Range, cell As Range
Dim sName As String

Set bk = ThisWorkbook
bk.SaveCopyAs Filename:="T:\Dummy.xlsm"
Set sh = bk.Worksheets("Createfiles")
Set r = sh.Range("A1", sh.Cells(41, "A").End(xlUp))
On Error GoTo ErrHandler
For Each cell In r
 sName = ""
 If Len(cell.Text) > 0 Then
   sName = cell.Text & ".xlsm"
   Workbooks.Open "T:\Dummy.xlsm"
   Set bk1 = ActiveWorkbook
   Debug.Print bk1.Name, sName
   bk1.SaveAs Filename:="T:\" & sName, _
     FileFormat:=xlOpenXMLWorkbookMacroEnabled
   bk1.Close SaveChanges:=False
 End If
Next
Kill "T:\Dummy.xlsm"
Exit Sub
ErrHandler:
MsgBox "Error - problem with file: " & sName
Resume Next
End Sub

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.