SOLVED
Home

Macro Using a Relative Function for all Sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-229420%22%20slang%3D%22en-US%22%3EMacro%20Using%20a%20Relative%20Function%20for%20all%20Sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-229420%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20create%20a%20macro%20that%20goes%20to%20the%20next%20sheet%2C%20but%20I%20can%20only%20create%20a%20macro%20that%20goes%20to%20a%20specific%20sheet%2C%20regardless%20of%20whether%20or%20not%20I'm%20selecting%20the%20%22Use%20Relative%20References%22%20button%20when%20creating%20the%20macro.%20How%20can%20I%20create%20the%20macro%20function%20I%20want%2C%20perpetuating%20going%20to%20next%20sheet%2C%20from%20any%20sheet%20in%20the%20Workbook%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-229420%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacro%20Sheet%20%22Using%20Relative%20Reference%22%20equivalent%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-229948%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20Using%20a%20Relative%20Function%20for%20all%20Sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-229948%22%20slang%3D%22en-US%22%3EThank%20you!%3CBR%20%2F%3EHad%20to%20tweak%20the%20macro%20a%20little%20bit%20to%20get%20the%20code%20to%20work%20exactly%20as%20I%20was%20wanting%3A%3CBR%20%2F%3E%3CBR%20%2F%3ESub%20NextSheet()%3CBR%20%2F%3E'%3CBR%20%2F%3E'%20NextSheet%20Macro%3CBR%20%2F%3E'%3CBR%20%2F%3E'%20Keyboard%20Shortcut%3A%20Ctrl%2BShift%2BL%3CBR%20%2F%3E'%3CBR%20%2F%3EnextSheetIndexNumber%20%3D%20ActiveSheet.Index%20%2B%201%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20nextSheetIndexNumber%20%26gt%3B%20Sheets.Count%20Then%3CBR%20%2F%3ESheets(1).Activate%3CBR%20%2F%3EElse%3CBR%20%2F%3ESheets(nextSheetIndexNumber).Activate%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-229428%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20Using%20a%20Relative%20Function%20for%20all%20Sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-229428%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Ana%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20this%20code%20below%20and%20find%20it%20also%20in%20the%20attached%20file.%3C%2FP%3E%3CPRE%3ESub%20GoToTheNextSheet()%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20nextSheetIndexNumber%20As%20Integer%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20nextSheetIndexNumber%20%3D%20ActiveSheet.Index%20%2B%201%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20If%20nextSheetIndexNumber%20%26gt%3B%20Sheets.Count%20Then%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Sheets(1).Activate%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Else%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Sheets(nextSheetIndexNumber).Activate%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20End%20If%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20code%20allows%20you%20to%20go%20to%20the%20next%20sheet%20of%20the%20active%20sheet.%3C%2FP%3E%3CP%3EIf%20the%20active%20sheet%20is%20the%20last%20sheet%20in%20the%20workbook%2C%20this%20code%20will%20move%20you%20to%20the%20very%20first%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E
Ana McCorkhill
New Contributor

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?

2 Replies

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

Solution
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
Related Conversations