How to replace multiple macros with one by creating two new subs

[Waimea]

Hi, I am using macros to go between sheets and I have 20 separate macros for going to different sheets and I have an additional 20 sheets to go back to the home sheet.

I would like some help with how to create two macro/subroutines that replaces these 40 macros with two new macros.

The first macro should take sheet name, scroll area, and tab color as arguments. The second macro should take sheet name and tab color as arguments.

Sub StartOption1()
    ActiveWorkbook.worksheets("Option1").Visible = True
    ActiveWorkbook.worksheets("Home").Visible = False
    worksheets("Option1").Activate
    Sheets("Option1").ScrollArea = "C1:X37"
    Sheets("Option1").Tab.Color = RGB(182, 173, 165)
End Sub


Sub StarOption1Back()
    ActiveWorkbook.worksheets("Home").Visible = True
    ActiveWorkbook.worksheets("Option1").Visible = False
    worksheets("Home").Activate
    Sheets("Home").Tab.Color = RGB(182, 173, 165)   
End Sub

 

[Waimea]

I think I understand how to pass the parameters to the sub but not how to use them in the sub.

Something like this?

Private Sub GoToTest(sheetname as worksheet, optional scroll_area as range, optional tab_color as string)
worksheets(sheetname).Visible = True
worksheets(sheetname).ScrollArea = scroll_area
worksheets(sheetname).Tab.Color= tab_color
worksheets(sheetname).Activate
End sub

Public Sub GobackTest(sheetname as worksheet, optional tab_color as string)
worksheets(sheetname).Visible = True

worksheets(sheetname).Activate

End sub

 

[Fluff]

Try it like

Private Sub GoToTest(sheetname As Worksheet, Optional scroll_area As String, Optional tab_color As String)
sheetname.Visible = True
sheetname.ScrollArea = scroll_area
sheetname.Tab.Color = tab_color
sheetname.Activate
End Sub
Sub chk()
   Call GoToTest(Sheets("pcode"), "C1:X37", RGB(182, 173, 165))
End Sub

 

[Waimea]

Hi Fluff,

thank you very much for your example! I now better understand how to pass arguments to subs and how to try to remove unnecessary code!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Waimea]

Can I set visibility to xlVeryHidden?

 

[Waimea]

What I am asking is if I can use the same macro to hide a sheet?

 

[Fluff]

Yes you can but then you would not be able to make the sheet active.

 

[Waimea]

Hi Fluff,

thank you for your answer!

If I wanted to hide the activesheet to be xlVeryHidden and to activate the sheet called start, what would I change?

 

[Fluff]

Try

Private Sub GoToTest(NewWs As Worksheet, OldWs As Worksheet, Optional scroll_area As String, Optional tab_color As String)
NewWs.Visible = True
NewWs.ScrollArea = scroll_area
If tab_color <> "" Then NewWs.Tab.Color = tab_color
NewWs.Activate
OldWs.Visible = xlSheetVeryHidden
End Sub
Sub chk()
   Call GoToTest(Sheets("pcode"), ActiveSheet)
End Sub