Selecting a worksheet based on the sheet Number

Q

QuietMan

Can anyone tell me why the code does not work

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

Thanks
 
G

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
 
Q

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
 
G

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
 
Q

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
 
G

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
 
Q

QuietMan

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

Thanks for the help
 
Q

QuietMan

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

Gary Keramidas

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

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
 
Top