duplicate spreadsheets with different names

[davegb]

davegb Mar 9, 12:19 pm show options

Newsgroups: microsoft.public.excel
From: "davegb" <[email protected]> - Find messages by this author
Date: 9 Mar 2005 12:19:22 -0800
Local: Wed, Mar 9 2005 12:19 pm
Subject: duplicate spreadsheets with different names
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse

I'm creating spreadsheets for each county in the state (64). I've
created a master spreadsheet, with macros and buttons to make things
easier for state employees. Now, I need to make 63 copies of this
spreadsheet, one for each county, with the county name in a merged cell

in the upper left corner (where A1 would be) of each worksheet,
following other descriptive text. Each spreadsheet will be named by
county and put in a county folder.


To clarify, I need a macro to create 64 folders, one for each county (I

already know how to do that), then take a standard workbook, or
template, give it the county name along with other descriptive text,
put the county name in following other text in the first cell in each
sheet in the workbook, and place it in the appropriate county folder.


Of course, I'm going to set up and test this on my local hard drive
with about 5 "counties", and after it works there, change the path for
the folders and spreadsheets to go to, the server, and do the actual
creation of the user files there.


Can anyone help me with this? Thanks in advance!

 

[Guest]

I would start with a Named Range, (Counties) in your Master.xls

Then

Dim oCell as Range
For each oCell in Range("Counties")
ThisWorkbook.SaveAs (FileName:=oCell.Value)
Activeworkbook.Range("A1")=oCell.Value
''' More Code
Activeworkbook.Close True
Next oCell

Hope this helps get you started

 

[Tom Ogilvy]

Might be faster to save it only once and probably should stick with
activeworkbook

Dim oCell as Range
Dim sPath as String
sPath = "M:\MyFolder\"
For each oCell in Range("Counties")
workbooks.Open("C:\MyFolder\MyTemplate.xls")
for each sh in ActiveWorkbook
sh.Range("A1")=oCell.Value
Next
''' More Code
ActiveWorkbook.SaveAs (FileName:=sPath & oCell.Value & "\" & oCell.Value)
' workbook is saved, so just close it
Activeworkbook.Close Savechanges:=False
Next oCell