defining variable in sub

[sspicer]

I have a lot of repetitive code and was trying to clean it up by creating a sub with a variable, I'm not sure whether I'm correct in my thinking but would love some help with the below, I've written the following code to navigate between worksheets:

Sub MoveTo(Target As Worksheet)
    Sheets(Target).Activate
    UnhideAll
    HideOtherSheets
End Sub

where UnhideAll and HideOtherSheets both work in their own right, and indeed if I put an actual sheet name in place of target the sub works.

Then, I have the following code upon opening:

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    
'ask whether a new session should be started or not
    
    Message = MsgBox("Do you wish to begin a new session?" & Chr(10) & Chr(10) & "Clicking Yes will clear all existing data", vbYesNo, "Warning!")
        
        If Message = vbYes Then
            Sheets("Scheme Input").Range("C5:C21").ClearContents
            Sheets("Member Data").Range("H3:H10000").ClearContents
            Sheets("Member Data").Range("J3:L10000").ClearContents
        Else: End If
    
MoveTo (Control)
'remember calculation mode of excel upon opening
    
    If Application.Calculation = xlManual Then
        Application.Calculation = xlAutomatic
        StartCalcMode = "Manual"
    Else: StartCalcMode = "Automatic"
    End If
    
End Sub

and I keep getting an error that says "object variable or with block variable not set".

Any ideas??

 

[RoryA]

You are passing a Worksheet object, not the name of a sheet, so this line:

Sheets(Target).Activate

should be:

Target.Activate

 

[sspicer]

Thank you! Works a dream (although changed the variable to string instead of other way around, but I guess would work either way).

Sub MoveTo(Target As String)
    Sheets(Target).Activate
    UnhideAll
    HideOtherSheets
End Sub