Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Macro to move to next sheet

  1. #1
    Board Regular lPinkFloyd's Avatar
    Join Date
    Nov 2003
    Location
    Tucson, AZ
    Posts
    92
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro to move to next sheet

    I tried to record a macro to move to the next sheet in the workbook, but couldn't get it to take. I got an error 28 something about stack error. I

    want to make one to move forward and another to move backward by one sheet at a time. Also the record time seened to take forever.
    I'll see you on the "Dark Side of the Moon."

  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to move to next sheet

    Hi IPinkFloyd:

    How about trying ...
    Code:
    activesheet.next.select
    and
    Code:
    activesheet.previous.select
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  3. #3
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,176
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Macro to move to next sheet

    To avoid run time errors when the active sheet is the last (trying to go next) or the first (trying to go the the previous), then this worked when tested. Assumes no sheets are hidden...if there are hidden sheets post back for an addended code.


    Next sheet:

    Sub Test1()
    On Error Resume Next
    Sheets(ActiveSheet.Index + 1).Activate
    If err.number <> 0 Then Sheets(1).Activate
    End Sub


    Previous sheet:

    Sub Test2()
    On Error Resume Next
    Sheets(ActiveSheet.Index - 1).Activate
    If err.number <> 0 Then Sheets(Worksheets.Count).Activate
    End Sub

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to move to next sheet

    Hi Tom:

    Good caution -- as always -- Thanks!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  5. #5
    Board Regular
    Join Date
    Jul 2003
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to move to next sheet

    how would you get around a hidden sheet, would you unhide them all go to next then rehide

    just curious

    thanks

    JOn

  6. #6
    Board Regular
    Join Date
    Apr 2009
    Posts
    441
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to move to next sheet

    Quote Originally Posted by Tom Urtis View Post
    To avoid run time errors when the active sheet is the last (trying to go next) or the first (trying to go the the previous), then this worked when tested. Assumes no sheets are hidden...if there are hidden sheets post back for an addended code.
    I need this code but to let it skip hidden sheets. How do I do it?

  7. #7
    Board Regular
    Join Date
    Jan 2008
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to move to next sheet

    Quote Originally Posted by silkfire View Post
    I need this code but to let it skip hidden sheets. How do I do it?
    Hi and sorry to bump an old thread but I was wondering if the above is possible.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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