Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
duplicate spreadsheets with different names
davegb Mar 9, 12:19 pm show options
Newsgroups: microsoft.public.excel From: "davegb" - 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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
duplicate spreadsheets with different names
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 "davegb" wrote: davegb Mar 9, 12:19 pm show options Newsgroups: microsoft.public.excel From: "davegb" - 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
duplicate spreadsheets with different names
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 "gocush" /delete wrote in message ... 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 "davegb" wrote: davegb Mar 9, 12:19 pm show options Newsgroups: microsoft.public.excel From: "davegb" - 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate data in multiple spreadsheets | Excel Worksheet Functions | |||
How do I get rid of duplicate spreadsheets? | Excel Discussion (Misc queries) | |||
Comparing 2 spreadsheets to remove duplicate names | Excel Discussion (Misc queries) | |||
How do you delete duplicate addresses, but keep duplicate names? | Excel Discussion (Misc queries) | |||
finding duplicate data in multiple spreadsheets | Excel Programming |