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.

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.

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.