Selecting a worksheet based on the sheet Number

[QuietMan]

Can anyone tell me why the code does not work

TTK = 40
Sht_Select = "Sheet" & TTK
Sheets(Sht_Select).Select

Thanks

 

[Gary''s Student]

Your code is good. Does Sheet40 exist???

 

[Gary Keramidas]

this worked for me

Sub test()
Dim TTK As String
Dim Sht_Select As String
TTK = 2
Sht_Select = "Sheet" & TTK
Sheets(Sht_Select).Select
End Sub

 

[QuietMan]

Here is a sample the code Im working with...for some reason it will not
work....
Sub PrepSystemWide()
Application.ScreenUpdating = False
For X = 1 To 3
Select Case X
Case Is = 1: TTK = 40
Case Is = 2: TTK = 125
Case Is = 3: TTK = 127
End Select
Sht_Select = "Sheet" & TTK
Sheets(Sht_Select).Select
ActiveSheet.PageSetup.CenterFooter = "Page &P of &N"
Next X
Sheet3.Select
Application.ScreenUpdating = True
End Sub

 

[Gary Keramidas]

how about this?

Sub PrepSystemWide()
Application.ScreenUpdating = False
Dim sh As Worksheet
For Each sh In Worksheets(Array("Sheet1", "Sheet2", "Sheet3"))
sh.PageSetup.CenterFooter = "Page &P of &N"
Next
Sheet3.Select
Application.ScreenUpdating = True
End Sub

 

[QuietMan]

Olny issue is there are about 150 sheets in this file and I have to format
each sets of sheets differently when I print them. That why I was using the
case select so i couls range the numbers of sheet for each distinct formatting

Thanks, but looks like there is osmething about this file that causing the
macro not to work, because it works fine in a blank workbook

 

[Gary Keramidas]

how about something like this then?

Sub PrepSystemWide()
Application.ScreenUpdating = False
Dim i As Long

For i = 1 To Worksheets.Count
Select Case i

Case 1, 3

Worksheets(i).PageSetup.CenterFooter = "Page &P of &N"
Case 2
Worksheets(i).PageSetup.LeftFooter = "Page &P of &N"
End Select
Next
Sheet3.Select
Application.ScreenUpdating = True
End Sub

 

[QuietMan]

Thanks...I give this a try
Hopefully I doesn't reference the sheet position

Thanks for the help

 

[QuietMan]

Sorry It did not work as it select bases on relatively position of the sheet
rhtner that sheet number

 

[Gary Keramidas]

well, i have no idea whether you're using the sheet index number, sheet name or
the sheet codename.

 

[QuietMan]

I'm using the sheet codename...

Thanks again

 

[Gary Keramidas]

not sure if this will help you or not:

Sub PrepSystemWide()
Application.ScreenUpdating = False
Dim i As Long
Dim Sht_Select As Variant
For i = 1 To Worksheets.Count
On Error Resume Next
Sht_Select = ThisWorkbook.VBProject _
.VBComponents("Sheet" & i).Properties("Name").Value
On Error GoTo 0
Select Case i
Case 1, 3
Sheets(Sht_Select).PageSetup.RightFooter = "Page &P of &N"
Case 2
Sheets(Sht_Select).PageSetup.LeftFooter = "Page &P of &N"
End Select
Next
Sheet3.Select
Application.ScreenUpdating = True
End Sub