| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a worksheet based on the sheet Number
Can anyone tell me why the code does not work
TTK = 40 Sht_Select = "Sheet" & TTK Sheets(Sht_Select).Select Thanks -- Helping Is always a good thing |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting a worksheet based on the sheet Number
Your code is good. Does Sheet40 exist???
-- Gary''s Student - gsnu200819 "QuietMan" wrote: Can anyone tell me why the code does not work TTK = 40 Sht_Select = "Sheet" & TTK Sheets(Sht_Select).Select Thanks -- Helping Is always a good thing |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting a worksheet based on the sheet Number
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 -- Gary "QuietMan" wrote in message ... Can anyone tell me why the code does not work TTK = 40 Sht_Select = "Sheet" & TTK Sheets(Sht_Select).Select Thanks -- Helping Is always a good thing |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting a worksheet based on the sheet Number
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 -- Helping Is always a good thing "Gary Keramidas" wrote: 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 -- Gary "QuietMan" wrote in message ... Can anyone tell me why the code does not work TTK = 40 Sht_Select = "Sheet" & TTK Sheets(Sht_Select).Select Thanks -- Helping Is always a good thing |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting a worksheet based on the sheet Number
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 -- Gary "QuietMan" wrote in message ... 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 -- Helping Is always a good thing "Gary Keramidas" wrote: 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 -- Gary "QuietMan" wrote in message ... Can anyone tell me why the code does not work TTK = 40 Sht_Select = "Sheet" & TTK Sheets(Sht_Select).Select Thanks -- Helping Is always a good thing |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting a worksheet based on the sheet Number
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 -- Helping Is always a good thing "Gary Keramidas" wrote: 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 -- Gary "QuietMan" wrote in message ... 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 -- Helping Is always a good thing "Gary Keramidas" wrote: 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 -- Gary "QuietMan" wrote in message ... Can anyone tell me why the code does not work TTK = 40 Sht_Select = "Sheet" & TTK Sheets(Sht_Select).Select Thanks -- Helping Is always a good thing |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting a worksheet based on the sheet Number
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 -- Gary "QuietMan" wrote in message ... 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 -- Helping Is always a good thing "Gary Keramidas" wrote: 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 -- Gary "QuietMan" wrote in message ... 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 -- Helping Is always a good thing "Gary Keramidas" wrote: 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 -- Gary "QuietMan" wrote in message ... Can anyone tell me why the code does not work TTK = 40 Sht_Select = "Sheet" & TTK Sheets(Sht_Select).Select Thanks -- Helping Is always a good thing |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting a worksheet based on the sheet Number
Thanks...I give this a try Hopefully I doesn't reference the sheet position Thanks for the help -- Helping Is always a good thing "Gary Keramidas" wrote: 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 -- Gary "QuietMan" wrote in message ... 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 -- Helping Is always a good thing "Gary Keramidas" wrote: 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 -- Gary "QuietMan" wrote in message ... 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 -- Helping Is always a good thing "Gary Keramidas" wrote: 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 -- Gary "QuietMan" wrote in message ... Can anyone tell me why the code does not work TTK = 40 Sht_Select = "Sheet" & TTK Sheets(Sht_Select).Select Thanks -- Helping Is always a good thing |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting a worksheet based on the sheet Number
Sorry It did not work as it select bases on relatively position of the sheet
rhtner that sheet number -- Helping Is always a good thing "Gary Keramidas" wrote: 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 -- Gary "QuietMan" wrote in message ... 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 -- Helping Is always a good thing "Gary Keramidas" wrote: 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 -- Gary "QuietMan" wrote in message ... 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 -- Helping Is always a good thing "Gary Keramidas" wrote: 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 -- Gary "QuietMan" wrote in message ... Can anyone tell me why the code does not work TTK = 40 Sht_Select = "Sheet" & TTK Sheets(Sht_Select).Select Thanks -- Helping Is always a good thing |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting a worksheet based on the sheet Number
well, i have no idea whether you're using the sheet index number, sheet name or
the sheet codename. -- Gary "QuietMan" wrote in message ... Sorry It did not work as it select bases on relatively position of the sheet rhtner that sheet number -- Helping Is always a good thing "Gary Keramidas" wrote: 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 -- Gary "QuietMan" wrote in message ... 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 -- Helping Is always a good thing "Gary Keramidas" wrote: 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 -- Gary "QuietMan" wrote in message ... 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 -- Helping Is always a good thing "Gary Keramidas" wrote: 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 -- Gary "QuietMan" wrote in message ... Can anyone tell me why the code does not work TTK = 40 Sht_Select = "Sheet" & TTK Sheets(Sht_Select).Select Thanks -- Helping Is always a good thing |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting a worksheet based on the sheet Number
I'm using the sheet codename...
Thanks again -- Helping Is always a good thing "Gary Keramidas" wrote: well, i have no idea whether you're using the sheet index number, sheet name or the sheet codename. -- Gary "QuietMan" wrote in message ... Sorry It did not work as it select bases on relatively position of the sheet rhtner that sheet number -- Helping Is always a good thing "Gary Keramidas" wrote: 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 -- Gary "QuietMan" wrote in message ... 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 -- Helping Is always a good thing "Gary Keramidas" wrote: 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 -- Gary "QuietMan" wrote in message ... 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 -- Helping Is always a good thing "Gary Keramidas" wrote: 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 -- Gary "QuietMan" wrote in message ... Can anyone tell me why the code does not work TTK = 40 Sht_Select = "Sheet" & TTK Sheets(Sht_Select).Select Thanks -- Helping Is always a good thing |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting a worksheet based on the sheet Number
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 -- Gary "QuietMan" wrote in message ... I'm using the sheet codename... Thanks again -- Helping Is always a good thing "Gary Keramidas" wrote: well, i have no idea whether you're using the sheet index number, sheet name or the sheet codename. -- Gary "QuietMan" wrote in message ... Sorry It did not work as it select bases on relatively position of the sheet rhtner that sheet number -- Helping Is always a good thing "Gary Keramidas" wrote: 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 -- Gary "QuietMan" wrote in message ... 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 -- Helping Is always a good thing "Gary Keramidas" wrote: 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 -- Gary "QuietMan" wrote in message ... 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 -- Helping Is always a good thing "Gary Keramidas" wrote: 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 -- Gary "QuietMan" wrote in message ... Can anyone tell me why the code does not work TTK = 40 Sht_Select = "Sheet" & TTK Sheets(Sht_Select).Select Thanks -- Helping Is always a good thing |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Rename Worksheet without Selecting the sheet | Excel Programming | |||
| Rename Worksheet without Selecting the sheet | Excel Programming | |||
| Selecting sheets based on cell in each sheet | Excel Programming | |||
| Selecting a number of rows based on the content | Excel Programming | |||
| Selecting a sheet based on a cell | Excel Programming | |||
Linear Mode
