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]

Hi Fluff,

thank you for your reply and your updated code!

I get a runtime error 438: object doesn´t support this property or method? when I run chk.

I have 20 documents that I call chk with, but back to my start page named "Start", I would like to use the same macro that always hides the active sheet and then activates the start sheet.

 

[Fluff]

Which line of code gives the error?

 

[Waimea]

Hi Fluff,

this line gives me the error:

 Call GoToTest(Sheets("Start"), ActiveSheet)

Both sheets are visible.

 

[Fluff]

Did you change the GoToTest macro?

 

[Waimea]

No, I missed that. Now it works really well!

Thank you Fluff!

 

[Fluff]

You're welcome

 

[Waimea]

I am wondering if there is possible to always have one tab is shown and one tab that switches?

So that my Start tab is always the first tab and the second tab changes from tab1 to tab something??

 

[Fluff]

I don't understand what you mean.

 

[Waimea]

Hi Fluff,

the code you created works great and shows only one tab at a time.

I am asking if it would be possible to have two visible tabs at all times, always the start tab and then the other tab that changes.

So when I am on the start tab the start tab is visible and when I click an option the new tab is opened in the other tab.

At all times there are two, not one, tabs visible?

 

[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
If OldWs.Name <> "start" Then OldWs.Visible = xlSheetVeryHidden
End Sub