Macro to call worksheet when value entered

[bluepenink]

Hello

I have a few worksheets in my workbook where i would like a macro in place, where, when i enter a value in cell X21, X31, X41, X51, X61...i want it to call sheet 55

sheet 55 = Contract sheet

so i would like sheet 2 to sheet 20 when cell X21, X31, X41, X51, X61 contain a number greater than 0 to simply go on the sheet 55

can someone pls help!

thx u

 

[Weaver]

So, do you want sheet 55 to open (for the user to go to 55) or for the data to be passed there (data goes to 55)? I'm afraid for me it's not clear from your description.

 

[bluepenink]

hello

sorry for the confusion.


basically, if a user from sheet 2 to sheet 22 enters a value greater than 0 in cells X21, X31, X41, X51, X61

i want when simply the macro to take the user to sheet 55 (contract sheet).

can u pls help?! let me know thx u

 

[Weaver]

Sheets 2 - 22, what are their names?

"sheet2", "sheet 2" or something else?

 

[Weaver]

Open up the vb editor, right click 'thisworkbook', choose 'view code' and paste in the following

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim r As Long, i As Long
    r = Val(Replace(Sh.Name, "Sheet", "", vbTextCompare))
    If r > 1 And r < 23 And Target.Column = 24 Then
        Select Case Target.Row
            Case 21, 31, 41, 51, 61
                Sheet55.Activate
        End Select
    End If
End Sub