Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy worksheet template to all files
Hi all
I have seen a number of solutions that address variations of my requirement, but can't find one that deals with it precisely or that I can adapt with my limited knowledge. I have a folder that contains about 100 workbooks. Each workbook contains a sheet for each month, with the latest sheet being called "JUL 06". There are no other files in this folder. I have a separate workbook (which can be in any location from my POV) which contains only one worksheet, named "AUG 06". This is to be used as a template for a new sheet in the 100 books. So what I want to do is copy the sheet "AUG 06" and insert it as the last sheet in each workbook (and, since the 100 workbooks contain links, suppress the update links dialogue). I will repeat this each month, so would be nifty if we can include a request to specify the name of the sheet to be copied. Alternatively, maybe I should specify the name of the new worksheet as it is created in each book, so I don't need to maintain the template. As always, your expert help is eagerly anticipated and gratefully received. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy worksheet template to all files
Split your requirements up into manageable stages and get each working
before trying to run the whole thing. 1. Locate template file: Either give the user the Application.GetOpenFileName dialog to locate it for you, or assume it in one of Excel's paths Application.TemplatesPath Application.AltStartupPath.. etc 2. Locate the folder to process, either with the API BrowseForFolders or an Excel method. 3. Open the template file from #1 4. Using Dir(), get the first/next WB that matches you criteria, from the folder in #2. 5. Open the WB from #4, with UpdateLinks=False. 6. Move, creating a copy, "AUG 06" to end of WB in #5. 7. Rename WS in #6 to =UCase (Format(Now(), "mmm yy")) 8. Close WB in #5, saving changes. 9. Return to #4, until Dir()="". 10. Close WB from #3 11. Add error handling in case any stage fails. The macro recorder will give workable code for much of this. NickHK "KeenKiwi" wrote in message ... Hi all I have seen a number of solutions that address variations of my requirement, but can't find one that deals with it precisely or that I can adapt with my limited knowledge. I have a folder that contains about 100 workbooks. Each workbook contains a sheet for each month, with the latest sheet being called "JUL 06". There are no other files in this folder. I have a separate workbook (which can be in any location from my POV) which contains only one worksheet, named "AUG 06". This is to be used as a template for a new sheet in the 100 books. So what I want to do is copy the sheet "AUG 06" and insert it as the last sheet in each workbook (and, since the 100 workbooks contain links, suppress the update links dialogue). I will repeat this each month, so would be nifty if we can include a request to specify the name of the sheet to be copied. Alternatively, maybe I should specify the name of the new worksheet as it is created in each book, so I don't need to maintain the template. As always, your expert help is eagerly anticipated and gratefully received. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy worksheet template to all files
See
http://www.rondebruin.nl/copy4.htm Read also the Tips -- Regards Ron de Bruin http://www.rondebruin.nl "KeenKiwi" wrote in message ... Hi all I have seen a number of solutions that address variations of my requirement, but can't find one that deals with it precisely or that I can adapt with my limited knowledge. I have a folder that contains about 100 workbooks. Each workbook contains a sheet for each month, with the latest sheet being called "JUL 06". There are no other files in this folder. I have a separate workbook (which can be in any location from my POV) which contains only one worksheet, named "AUG 06". This is to be used as a template for a new sheet in the 100 books. So what I want to do is copy the sheet "AUG 06" and insert it as the last sheet in each workbook (and, since the 100 workbooks contain links, suppress the update links dialogue). I will repeat this each month, so would be nifty if we can include a request to specify the name of the sheet to be copied. Alternatively, maybe I should specify the name of the new worksheet as it is created in each book, so I don't need to maintain the template. As always, your expert help is eagerly anticipated and gratefully received. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Possible to copy worksheet into template? | Excel Discussion (Misc queries) | |||
Copy Filterd Data from worksheet to another workbook template | Excel Discussion (Misc queries) | |||
Macro in a worksheet template that draws data from other files? | New Users to Excel | |||
How do I edit a worksheet and copy to multiple files? | Excel Discussion (Misc queries) | |||
Copy worksheet from multiple files in one DIR to another DIR & rename | Excel Programming |