Announcement

Collapse
No announcement yet.

VBA Copy Worksheet Without Adding New Workbook In VBA Project

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

    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.
    • 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

    Code:
        Worksheets("ProForma").Visible = True
        Worksheets("ProForma").Select
        Worksheets("ProForma").Copy After:=Sheets("Controls")
        Worksheets("ProForma (2)").Select
        Worksheets("ProForma (2)").Name = vAccountName
        Worksheets("ProForma").Visible = False


    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
    Attached Files
    Last edited by Dave Hawley; October 17th, 2008, 08:28.

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

    Hi Fredm400,

    Very difficult to debug without seeing code, but even with reviewing your code, it is a big task...

    Does this happen on all machines? (rule out machine specific issue).
    Does this happen on all versions of Excel (rule out Excel 2003 issue... e.g. Excel 2007 is supposed to handle larger numbers of rows/columns better).
    Does this happen every single time?
    How / where is the third party app used... do you have the source code, can you debug this.. (rule out that app if possible).

    In relation to adding worksheets.. one observation.

    Code:
    Worksheets("ProForma").Visible = True
    Worksheets("ProForma").Select
    Worksheets("ProForma").Copy After:=Sheets("Controls")
    Worksheets("ProForma (2)").Select
    Worksheets("ProForma (2)").Name = vAccountName
    Worksheets("ProForma").Visible = False
    Your code is slightly clunky - in my humble opinion. When you copy a worksheet there is no need to select. Additionally when you copy a worksheet, the "Copied" worksheet becomes active, so there is no need to refer to the copied worksheet name to rename it... assuming you are renaming it straight away... this code does exactly the same thing (I think).

    Code:
    Worksheets("ProForma").Visible = True
    Worksheets("ProForma").Copy after:=Sheets("Controls")
    ActiveSheet.Name = vAccountName
    Worksheets("ProForma").Visible = False
    Regardless, I would be dubious that this would be causing the issues you are seeing.

    I cant think of anything else off hand... except that having a workbook with 1000 worksheets is likely to be ineffective in terms of performance and filesize... (it will get very big and you wont be able to run any significant formulas). I dont know the exact limit on the number of sheets in one workbook in Excel 2003.

    Ger
    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

    Comment


    • #3
      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.

      Comment


      • #4
        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).

        Comment


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

          I wouldnt knock COBOL, it was one of the first languages I learned in College on an AS/400. But I wouldnt go back there for all the tea china (college and / or COBOL )

          If you want to email me worksheets I can try it tonight on Excel 2007. Let me know and I will PM you my email address. More so to Rule out Excel than anything else.

          Ger
          Check out our new reputation system. Click on the Like button under the post!
          _______________________________________________

          There are 10 types of people in the world. Those that understand Binary and those that dont.

          Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

          The BEST Lookup function of all time

          Dynamic Named Ranges are your bestest friend

          _______________________________________________

          Comment


          • #6


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

            Try

            Code:
            With ThisWorkbook.Worksheets("ProForma")
                .Visible = True 
                .Copy After:=Thisworkbook.Sheets("Controls") 
                Worksheets("ProForma (2)").name=vAccountName 
               .Visible=false
            End With

            Comment

            Working...
            X