+ Reply to Thread
Results 1 to 5 of 5

VBA Loop (?) - macro to run through 33 worksheets!

  1. #1
    Registered User
    Join Date
    10-23-2007
    Posts
    80

    VBA Loop (?) - macro to run through 33 worksheets!

    Hello everyone,

    I need to write a vba macro that will do the following:

    - I have a workbook with 36 sheets.
    - In 33 of those sheets there is a simple formula in C1 which returns a numerical value.
    - I need the macro to check cell C1 in each of those 33 sheets (so not the entire workbook), and if the value in that cell is zero it needs to hide that particular sheet before moving onto the next one.

    I know this is doable as I've done it before, but I've been away from the game for a while now and can't remember how I did it last time!!

    Thanks for your help!
    Last edited by lealea1982; 11-04-2010 at 01:36 PM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: VBA Loop (?) - macro to run through 33 worksheets!

    Hello lealea1982,

    This macro skips "Sheet1", "Sheet2", and "Sheet3". All other sheets have cell "C1" checked. If the value is zero then the sheet is hidden. Change the sheet names in bold below to the sheets you want to skip in your workbook.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: VBA Loop (?) - macro to run through 33 worksheets!

    Which sheets need excluding?

    Something like this
    Please Login or Register  to view this content.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: VBA Loop (?) - macro to run through 33 worksheets!

    Thanks for the rep points. If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

  5. #5
    Registered User
    Join Date
    10-23-2007
    Posts
    80

    Re: VBA Loop (?) - macro to run through 33 worksheets!

    Thank you so much for your help guys, that worked a treat. I now have another task which is similar so I may as well ask it in this thread too. This one may be a little more tricky, but you all seem to be some kind of Excel geniuses - so it's worth a try!!

    On one worksheet, I have ten defined names. Each name is basically a collection of rows (the whole row). So, for example, rows 9 to 18 have the name GoTo01, and rows 19 to 32 have the name GoTo02 (the number of rows varies in each name). What I want is a macro that will hide each name (so hide the rows) providing the total value is zero. So basically, I want it to select GoTo01 and if the sum of that is zero I want it to hide it and then move on to do the same thing for GoTo02... etc etc.

    Does that make sense? I know I haven't explained myself very well.

    Thanks again everyone!

+ 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