Sheet names in VBA scripts

**nickm84** · 06-01-2013, 05:53 PM

Hoping someone can help me.

I have the following code that relates to sheet names Sheet 1 - 20 in a workbook. However, in a new workbook, I have the sheet names as "ABC (1)", "ABC (2)" etc to 20. Can anyone kindly change the code below to reflect to the names of the sheets such as "ABC (1)" rather than "Sheet1".

Hope this makes sense.

Many thanks,

Nick


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i&
    
    If Intersect(Target, Range("C3:C4")) Is Nothing Then Exit Sub
    
    For i = 1 To 20     'enable in sheets marked "1"
        Worksheets("Sheet1").EnableCalculation = True
        Worksheets("Sheet" & i).EnableCalculation = False
    Next i

End Sub

**TMS** · 06-01-2013, 06:05 PM

Maybe:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i&

If Intersect(Target, Range("C3:C4")) Is Nothing Then Exit Sub

For i = 1 To 20     'enable in sheets marked "1"
    Worksheets("ABC (1)").EnableCalculation = True
    Worksheets("ABC (" & i & ")").EnableCalculation = False
Next i

End Sub

or even:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i&

If Intersect(Target, Range("C3:C4")) Is Nothing Then Exit Sub

Worksheets("ABC (1)").EnableCalculation = True
For i = 2 To 20     'enable in sheets marked "1"
    Worksheets("ABC (" & i & ")").EnableCalculation = False
Next i

End Sub

Regards, TMS