Forum Discussion

Ana McCorkhill's avatar
Ana McCorkhill
Copper Contributor
Aug 16, 2018

Macro Using a Relative Function for all Sheets

I want to create a macro that goes to the next sheet, but I can only create a macro that goes to a specific sheet, regardless of whether or not I'm selecting the "Use Relative References" button when creating the macro. How can I create the macro function I want, perpetuating going to next sheet, from any sheet in the Workbook?

  • Ana McCorkhill's avatar
    Ana McCorkhill
    Aug 16, 2018
    Thank you!
    Had to tweak the macro a little bit to get the code to work exactly as I was wanting:

    Sub NextSheet()
    '
    ' NextSheet Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+L
    '
    nextSheetIndexNumber = ActiveSheet.Index + 1

    If nextSheetIndexNumber > Sheets.Count Then
    Sheets(1).Activate
    Else
    Sheets(nextSheetIndexNumber).Activate
    End If

    End Sub
  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor

    Hi Ana,

     

    Please try this code below and find it also in the attached file.

    Sub GoToTheNextSheet()
        
        Dim nextSheetIndexNumber As Integer
        nextSheetIndexNumber = ActiveSheet.Index + 1
        
        If nextSheetIndexNumber > Sheets.Count Then
            Sheets(1).Activate
        Else
            Sheets(nextSheetIndexNumber).Activate
        End If

    End Sub

     

    This code allows you to go to the next sheet of the active sheet.

    If the active sheet is the last sheet in the workbook, this code will move you to the very first sheet.

     

    Hope that helps

    • Ana McCorkhill's avatar
      Ana McCorkhill
      Copper Contributor
      Thank you!
      Had to tweak the macro a little bit to get the code to work exactly as I was wanting:

      Sub NextSheet()
      '
      ' NextSheet Macro
      '
      ' Keyboard Shortcut: Ctrl+Shift+L
      '
      nextSheetIndexNumber = ActiveSheet.Index + 1

      If nextSheetIndexNumber > Sheets.Count Then
      Sheets(1).Activate
      Else
      Sheets(nextSheetIndexNumber).Activate
      End If

      End Sub

Resources