Copying sheets
 

Hi,
I'm new to the Excel group, I normally post and answer on the MSProject
newsgroup but my question involves a VBA issue creating and populating
an Excel workbook from a Project macro. I need to creat a variable
number of pre-formatted worksheets into which Project data will be
exported. My VBA code currently creates the formatted worksheet and then
uses the following loop to create the requisite number of pre-formatted
worksheets:

BookNam = ActiveWorkbook.Name
Set s = Workbooks(BookNam).Worksheets(1)
For i = 1 to ShtCnt
s.Copy after:=worksheets(i)
Next i

The code works fine most of the time but not always. If I open a fresh
instance of Excel it works and generally if I make successive runs it
executes successfully. I should mention that the code is structured to
create a new workbook each time it is run. However, if I delete one of
the workbooks from a previous run, the above loop executes without error
but does not generate any copies of pre-formatted worksheets. The
Project data is exported but after the first worksheet the data is just
dumped unto blank worksheets. Does anyone have a clue why the above code
doesn't always work?

I also tried another approach by using a template worksheet file.
Apparently however, even though I create the template for sheet 1, if I
add subsequent sheets (simple insert sheet, not copy sheet), they are
not duplicates of the template. The help file doesn't seem to cover
this. Is there a way to make all sheets of a variable sheet workbook
have a pre-formatted form?

I apologize for the long winded post. Any help is greatly appreciated.
Thanks in advance.
John

Copying sheets
 

Well, you have left one thing to chance:

BookNam = ActiveWorkbook.Name
Set s = Workbooks(BookNam).Worksheets(1)
For i = 1 to ShtCnt
s.Copy after:=s.parent.worksheets(i)
Next i

If you use a workbook template for the workbook and create the workbook with
say 10 sheets specified for the number of sheets to go into a new workbook,
then all the sheets should be formatted the same.

--
Regards
Tom Ogilvy

"John" wrote in message
...
Hi,
I'm new to the Excel group, I normally post and answer on the MSProject
newsgroup but my question involves a VBA issue creating and populating
an Excel workbook from a Project macro. I need to creat a variable
number of pre-formatted worksheets into which Project data will be
exported. My VBA code currently creates the formatted worksheet and then
uses the following loop to create the requisite number of pre-formatted
worksheets:

BookNam = ActiveWorkbook.Name
Set s = Workbooks(BookNam).Worksheets(1)
For i = 1 to ShtCnt
s.Copy after:=worksheets(i)
Next i

The code works fine most of the time but not always. If I open a fresh
instance of Excel it works and generally if I make successive runs it
executes successfully. I should mention that the code is structured to
create a new workbook each time it is run. However, if I delete one of
the workbooks from a previous run, the above loop executes without error
but does not generate any copies of pre-formatted worksheets. The
Project data is exported but after the first worksheet the data is just
dumped unto blank worksheets. Does anyone have a clue why the above code
doesn't always work?

I also tried another approach by using a template worksheet file.
Apparently however, even though I create the template for sheet 1, if I
add subsequent sheets (simple insert sheet, not copy sheet), they are
not duplicates of the template. The help file doesn't seem to cover
this. Is there a way to make all sheets of a variable sheet workbook
have a pre-formatted form?

I apologize for the long winded post. Any help is greatly appreciated.
Thanks in advance.
John