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
Highlighted

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
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies