+ Reply to Thread
Results 1 to 3 of 3

Macro fails to Loop Properly

  1. #1
    Registered User
    Join Date
    06-26-2012
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    2

    Macro fails to Loop Properly

    Hi all,

    Long time lurker, and finally the search function has let me down.

    I am having issues with a loop, the macro works correctly for one full loop then goes bonkers.

    I am handling the reporting of a project management company and we generally have 50-100 active projects that I need to generate a reporting on. Each report is made up of 8 different worksheets that I would like to combine into a single workbook per project. All the worksheets have their four digit project id in A1 as well as the code is in the name of the work sheet. IE '1826 Cover', '1826 - Man Hour Estimate'

    The macro I am trying to create will perform the following loop. Take a value from a list of active projects, find all worksheets with this same value in cell A1, move these worksheets to a new workbook, save and close the new workbook, return to the original and move to the next entry in the list of projects.


    Please Login or Register  to view this content.
    Currently the first loop works flawlessly, then on the second loop too many sheets are selected then everything crashes.

    Any quick hits?

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,031

    Re: Macro fails to Loop Properly

    Hi, fatrob,

    after the first loop your array of sheets is still filled when you use
    Please Login or Register  to view this content.
    Same goes for the counter i. In the next loop every hit will be entered into the array as well.

    Try using the statements
    Please Login or Register  to view this content.
    which should clear the contents of the array and start all over again.

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Registered User
    Join Date
    06-26-2012
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Macro fails to Loop Properly

    Thank you very much sir/mam, she looped through the entire list.

    Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1