+ Reply to Thread
Results 1 to 6 of 6

looping thru all sheets within a workbook

  1. #1
    Registered User
    Join Date
    07-15-2013
    Location
    mass, usa
    MS-Off Ver
    Excel 2007
    Posts
    5

    looping thru all sheets within a workbook

    I have a macro that is designed to read thru all active sheets in a workbook (ignoring the hidden sheets) but i am getting stuck when i come to the 'last' sheet, the macro aborts on the command i am using to move from the 1st sheet to the last sheet - ActiveSheet.Next.Select. What technique can be used to know when you have reached the last sheet or is there a way to get a count of the number of active sheets and set up a loop to read the correct number of sheets? This macro is intended to work on a collection of workbooks in one folder.

    Thanks in advance!!

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: looping thru all sheets within a workbook

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    07-15-2013
    Location
    mass, usa
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: looping thru all sheets within a workbook

    so after the last worksheet is read and processed ('the other bits of code') it will drop past the 'Next wsh' command and that is where i can close the workbook and read the next one in the folder?

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: looping thru all sheets within a workbook

    Adding to AB33's code:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  5. #5
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: looping thru all sheets within a workbook

    Yes in theory, but in practice looping through a folder is not as straight as looping though the active workbook.

  6. #6
    Registered User
    Join Date
    07-15-2013
    Location
    mass, usa
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: looping thru all sheets within a workbook

    Thanks for the quick replies!!!! You folks are AWESOME. i will give this a try and get back to you tomorrow as i am heading out for the day.
    Thanks again!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Looping through sheets to clear a specified range, but excluding some sheets
    By d.sanchez in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-03-2013, 02:39 PM
  2. [SOLVED] Looping Through Sheets in a workbook in Excel 2010
    By Johnr1 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-26-2012, 05:09 AM
  3. [SOLVED] Multiple Sheets be reflected in 1 sheets as a summary (looping)
    By Kooper in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-21-2012, 03:21 AM
  4. Looping through a workbook with sheets and charts
    By Masact in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-30-2009, 05:59 PM
  5. Looping a column in workbook 1 to workbook 2's sheets
    By L. Howard Kittle in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-15-2006, 11:15 AM

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