VBA Copy Worksheet Without Adding New Workbook In VBA Project

Environment:
XP Pro – SP3
Excel 2003
No other Excel workbooks are open at start of process.
Code developed and executed in same environment.

Process:
All VBA code resides in Workbook#1.

Workbook #1 (BasicAssignmentsV03.xls) has a list of 1,000+ accounts. End-user goes through list and indicates how they want the accounts grouped in the DataAnalysis file. Each grouping can have from 1 to 25 accounts associated.

Workbook#2 (BasicStmt-Master V04.xls) is a data analysis file. All of the business rules and analysis have been built into an existing worksheet named “ProForma”. The formulas and calculations on ProForma provide for the external definitions of the account and four custom codes that come from Workbook #1.

VBA code functions:

All VBA code resides in Workbook#1.

Code in workbook#1 will
• Sort the records on the HFMAssignments worksheet by the user-defined groupings.
• Open the workbook#2 master file
• For each account in the specified grouping[INDENT]
o Perform editing functions on the data.
o Create a duplicate of the ProForma worksheet that contains all the required business rules and sub-totals.
o Load the account specific variables into the data analysis cells.
o Update the account information via third-party add-in.[/INDENT]
• Saves Workbook#2 under new name.
• Clears the Workbook #2 controls and removes the added worksheets
• Starts in on the next account grouping from Workbook #1

Process works for low volume of accounts, but fails at full volume.

Troubleshooting to date:

Here what we have found so far.

This screen shot comes from just prior to adding the new worksheet into Workbook#2.

In the VBAProject pane, there are only 3 major objects listed; Workbooks #1 and #2, plus the third party add-in (HsTbar.xla)

(See attachment)

Code is...

'Create a worksheet for the account based on the "ProForma" template

Here is the status just after adding the worksheet to Workbook#2.
The Project pane not only shows the new worksheet in Workbook#2 (Proforma(2)), it also shows a new object called, “Book2”.

We do not understand where this new object came from. We expected the Sheets collection to grow by the new sheet in our BasicStmt-Master workbook, but not the creation of this new object.

As shown below, at the end of the first grouping, there are the expected thirteen new worksheets in the data analysis workbook (BasicStmt-Master), but there are also 13 occurrences of this new “Book2” object in the VBAProject window.

See attachment #2 for screen shot.

Eventually the Project fills with these new “Book2” objects and the code quits adding the worksheets.

Problem/Solution:
This is the first time I’ve tried to add 1,000+ worksheets spread over 50+ workbooks. I have not noticed this “Book2” phenomena before.

What are these “Book2” items in the Project window?

Is there a procedure/function/???? that can be executed periodically to remove these items from the Project

Is there a more productive way to add this volume of worksheets to workbooks?

A viable solution would be worth $50.00

Re: Add A New Worksheet Via Vba Without Also Adding A New Book To The Vba Project.

Ger,

Thanks for taking a look.
I agree this may not be an easy code fix. The code is working, just getting the additional items in the Project.
Could this be triggered by an Excel Setting?

Thanks for being diplimatic enough to say my code is "..slightly clunky". I learned to code in COBOL, the 1968 standard. While I have gotten away from using GOTO's and writing the entire thing in a set of complicated nested IF statements, I'm self-taught in VB and some bad habits die hard.

May not have been clear enough. Any single data analysis workbook will only have from 1 to a maximum of 20 worksheets. This grouping process breaks down the 1,000+ accounts, which are rows in the Assignment workbook, into the data analysis workbooks.

Re: Add A New Worksheet Via Vba Without Also Adding A New Book To The Vba Project.

Ger,

Sorry,

Yes - Happens on all machines.
Yes - Happens all the time.
Only have Excel 2003, so don't know about other versions.
Third party add-in source not available (Hyperion Smart View).