Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Nov 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    need macro to stop after last page (Excel97)

    I have a macro that formats a sheet, then goes on to the next one like this:

    ActiveSheet.Next.Select

    I will not know ow many sheets it has because it may change while it grows

    Well, it keeps running then crashes after the last sheet, How do I prevent this

    Thanks for any input

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: need macro to stop after last page (Excel97)

    Generally by using:

    Dim intC as Integer
    For intC = to ThisWorkbook.Worksheets.Count
    <process>
    Next intC

    but if you can describe more of what you are doing, perhaps post the relevant code, there may be a better way, for instance it usually is not necessary to activate each sheet.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    New Lounger
    Join Date
    Nov 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: need macro to stop after last page (Excel97)

    It is mainly formatting - things like: colors,fonts,borders, hiding, etc. I found that "selecting all pages didn't work because formatting all the pages at once didn't do everything like hiding columns. I am going to try your solution as it looks just what I was looking for (counting of existing sheets) but would be glad to hear any other input

    Thanks for your solution,

    John

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: need macro to stop after last page (Excel97)

    Or

    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
    'Process the sheet
    Next


    Andrew C

  5. #5
    New Lounger
    Join Date
    Nov 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: need macro to stop after last page (Excel97)

    Neither of these formulas are looping through the sheets, they just do the first sheet ?!?

    Am I doing something wrong?

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: need macro to stop after last page (Excel97)

    Neither is actually selecting any sheet, which is probably unnecessary in order to apply formatting. Try the following in a blank workbook.

    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
    ws.Rows(1).Interior.ColorIndex = 6
    Next

    If you run that you will find that each sheet has the first row formatted with a yellow background. If you run into any problem in applying your formatting using this approach, post back with the details.

    Andrew C

  7. #7
    New Lounger
    Join Date
    Nov 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: need macro to stop after last page (Excel97)

    THANK - YOU - Exactly what I was looking for.

Posting Permissions

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