Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveSheet.name not returning Sheet1, Sheet2
In VBE in the property window of a worksheet there is a field (Name) that
doesn't change even if the tab name or index position changes. I would like to use this "name" and evaluate it to reset the print range. I only want to reset the print range of Sheets "Sheet11" to "Sheet31". When I use the ActiveSheet.Name command I only get the "tab" name, not this static name. Is there any way to retrieve the (Name) and evaluate it. Here is the piece of code that I am working with. Dim SheetNum_Low As Integer Dim SheetNum_High As Integer Dim ws As Worksheet Dim SheetNum As Integer SheetNum_Low = 11 SheetNum_High = 31 For Each ws In Worksheets ws.Activate SheetNum = ActiveSheet.Index SheetNum = val(left(ActiveSheet.Name,2)) If SheetNum = SheetNum_Low And SheetNum <= SheetNum_High Then ActiveSheet.PageSetup.PrintArea = ActiveSheet.UsedRange.Address End If Next |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveSheet.name not returning Sheet1, Sheet2
Activesheet.codename
-- Regards, Tom Ogilvy "Rebecca_SUNY" wrote: In VBE in the property window of a worksheet there is a field (Name) that doesn't change even if the tab name or index position changes. I would like to use this "name" and evaluate it to reset the print range. I only want to reset the print range of Sheets "Sheet11" to "Sheet31". When I use the ActiveSheet.Name command I only get the "tab" name, not this static name. Is there any way to retrieve the (Name) and evaluate it. Here is the piece of code that I am working with. Dim SheetNum_Low As Integer Dim SheetNum_High As Integer Dim ws As Worksheet Dim SheetNum As Integer SheetNum_Low = 11 SheetNum_High = 31 For Each ws In Worksheets ws.Activate SheetNum = ActiveSheet.Index SheetNum = val(left(ActiveSheet.Name,2)) If SheetNum = SheetNum_Low And SheetNum <= SheetNum_High Then ActiveSheet.PageSetup.PrintArea = ActiveSheet.UsedRange.Address End If Next |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveSheet.name not returning Sheet1, Sheet2
That (Name) property is called the codename.
I think I'd use something like: Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets With wks If LCase(.CodeName) Like LCase("Sheet##") Then Select Case CLng(Right(.CodeName, 2)) Case 11 To 31 'do the work 'MsgBox .CodeName & "--" & .Name .PageSetup.PrintArea = .UsedRange.Address End Select End If End With Next wks Rebecca_SUNY wrote: In VBE in the property window of a worksheet there is a field (Name) that doesn't change even if the tab name or index position changes. I would like to use this "name" and evaluate it to reset the print range. I only want to reset the print range of Sheets "Sheet11" to "Sheet31". When I use the ActiveSheet.Name command I only get the "tab" name, not this static name. Is there any way to retrieve the (Name) and evaluate it. Here is the piece of code that I am working with. Dim SheetNum_Low As Integer Dim SheetNum_High As Integer Dim ws As Worksheet Dim SheetNum As Integer SheetNum_Low = 11 SheetNum_High = 31 For Each ws In Worksheets ws.Activate SheetNum = ActiveSheet.Index SheetNum = val(left(ActiveSheet.Name,2)) If SheetNum = SheetNum_Low And SheetNum <= SheetNum_High Then ActiveSheet.PageSetup.PrintArea = ActiveSheet.UsedRange.Address End If Next -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveSheet.name not returning Sheet1, Sheet2
I knew it was something easy but I just couldn't FIND it. Thanks. You went
above and beyond and it was very helpful. I assume the Lcase is because the Like operator is case sensitive...? "Dave Peterson" wrote: That (Name) property is called the codename. I think I'd use something like: Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets With wks If LCase(.CodeName) Like LCase("Sheet##") Then Select Case CLng(Right(.CodeName, 2)) Case 11 To 31 'do the work 'MsgBox .CodeName & "--" & .Name .PageSetup.PrintArea = .UsedRange.Address End Select End If End With Next wks Rebecca_SUNY wrote: In VBE in the property window of a worksheet there is a field (Name) that doesn't change even if the tab name or index position changes. I would like to use this "name" and evaluate it to reset the print range. I only want to reset the print range of Sheets "Sheet11" to "Sheet31". When I use the ActiveSheet.Name command I only get the "tab" name, not this static name. Is there any way to retrieve the (Name) and evaluate it. Here is the piece of code that I am working with. Dim SheetNum_Low As Integer Dim SheetNum_High As Integer Dim ws As Worksheet Dim SheetNum As Integer SheetNum_Low = 11 SheetNum_High = 31 For Each ws In Worksheets ws.Activate SheetNum = ActiveSheet.Index SheetNum = val(left(ActiveSheet.Name,2)) If SheetNum = SheetNum_Low And SheetNum <= SheetNum_High Then ActiveSheet.PageSetup.PrintArea = ActiveSheet.UsedRange.Address End If Next -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveSheet.name not returning Sheet1, Sheet2
That's what I would assume, too <vbg.
But it wouldn't take much to test it to confirm or reject that assumption! Rebecca_SUNY wrote: I knew it was something easy but I just couldn't FIND it. Thanks. You went above and beyond and it was very helpful. I assume the Lcase is because the Like operator is case sensitive...? "Dave Peterson" wrote: That (Name) property is called the codename. I think I'd use something like: Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets With wks If LCase(.CodeName) Like LCase("Sheet##") Then Select Case CLng(Right(.CodeName, 2)) Case 11 To 31 'do the work 'MsgBox .CodeName & "--" & .Name .PageSetup.PrintArea = .UsedRange.Address End Select End If End With Next wks Rebecca_SUNY wrote: In VBE in the property window of a worksheet there is a field (Name) that doesn't change even if the tab name or index position changes. I would like to use this "name" and evaluate it to reset the print range. I only want to reset the print range of Sheets "Sheet11" to "Sheet31". When I use the ActiveSheet.Name command I only get the "tab" name, not this static name. Is there any way to retrieve the (Name) and evaluate it. Here is the piece of code that I am working with. Dim SheetNum_Low As Integer Dim SheetNum_High As Integer Dim ws As Worksheet Dim SheetNum As Integer SheetNum_Low = 11 SheetNum_High = 31 For Each ws In Worksheets ws.Activate SheetNum = ActiveSheet.Index SheetNum = val(left(ActiveSheet.Name,2)) If SheetNum = SheetNum_Low And SheetNum <= SheetNum_High Then ActiveSheet.PageSetup.PrintArea = ActiveSheet.UsedRange.Address End If Next -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveSheet.name not returning Sheet1, Sheet2
Excel Help says Like is case INsensitive when comparing text, but that is not
how it tested. I tried it without the Lcase and changed it to "sheet##" and it returned false. Best to leave the lcase in. good tip. "Dave Peterson" wrote: That's what I would assume, too <vbg. But it wouldn't take much to test it to confirm or reject that assumption! Rebecca_SUNY wrote: I knew it was something easy but I just couldn't FIND it. Thanks. You went above and beyond and it was very helpful. I assume the Lcase is because the Like operator is case sensitive...? "Dave Peterson" wrote: That (Name) property is called the codename. I think I'd use something like: Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets With wks If LCase(.CodeName) Like LCase("Sheet##") Then Select Case CLng(Right(.CodeName, 2)) Case 11 To 31 'do the work 'MsgBox .CodeName & "--" & .Name .PageSetup.PrintArea = .UsedRange.Address End Select End If End With Next wks Rebecca_SUNY wrote: In VBE in the property window of a worksheet there is a field (Name) that doesn't change even if the tab name or index position changes. I would like to use this "name" and evaluate it to reset the print range. I only want to reset the print range of Sheets "Sheet11" to "Sheet31". When I use the ActiveSheet.Name command I only get the "tab" name, not this static name. Is there any way to retrieve the (Name) and evaluate it. Here is the piece of code that I am working with. Dim SheetNum_Low As Integer Dim SheetNum_High As Integer Dim ws As Worksheet Dim SheetNum As Integer SheetNum_Low = 11 SheetNum_High = 31 For Each ws In Worksheets ws.Activate SheetNum = ActiveSheet.Index SheetNum = val(left(ActiveSheet.Name,2)) If SheetNum = SheetNum_Low And SheetNum <= SheetNum_High Then ActiveSheet.PageSetup.PrintArea = ActiveSheet.UsedRange.Address End If Next -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveSheet.name not returning Sheet1, Sheet2
Doesn't VBA's help state that it's not case sensitive if "option compare text"
is used? Rebecca_SUNY wrote: Excel Help says Like is case INsensitive when comparing text, but that is not how it tested. I tried it without the Lcase and changed it to "sheet##" and it returned false. Best to leave the lcase in. good tip. "Dave Peterson" wrote: That's what I would assume, too <vbg. But it wouldn't take much to test it to confirm or reject that assumption! Rebecca_SUNY wrote: I knew it was something easy but I just couldn't FIND it. Thanks. You went above and beyond and it was very helpful. I assume the Lcase is because the Like operator is case sensitive...? "Dave Peterson" wrote: That (Name) property is called the codename. I think I'd use something like: Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets With wks If LCase(.CodeName) Like LCase("Sheet##") Then Select Case CLng(Right(.CodeName, 2)) Case 11 To 31 'do the work 'MsgBox .CodeName & "--" & .Name .PageSetup.PrintArea = .UsedRange.Address End Select End If End With Next wks Rebecca_SUNY wrote: In VBE in the property window of a worksheet there is a field (Name) that doesn't change even if the tab name or index position changes. I would like to use this "name" and evaluate it to reset the print range. I only want to reset the print range of Sheets "Sheet11" to "Sheet31". When I use the ActiveSheet.Name command I only get the "tab" name, not this static name. Is there any way to retrieve the (Name) and evaluate it. Here is the piece of code that I am working with. Dim SheetNum_Low As Integer Dim SheetNum_High As Integer Dim ws As Worksheet Dim SheetNum As Integer SheetNum_Low = 11 SheetNum_High = 31 For Each ws In Worksheets ws.Activate SheetNum = ActiveSheet.Index SheetNum = val(left(ActiveSheet.Name,2)) If SheetNum = SheetNum_Low And SheetNum <= SheetNum_High Then ActiveSheet.PageSetup.PrintArea = ActiveSheet.UsedRange.Address End If Next -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy data from sheet2 to sheet1 when sheet2 has variable # of rows | Excel Discussion (Misc queries) | |||
A1 Sheet2 is linked to A1 sheet1 so that user enters value(abc123) a1 sheet1 and A1 sheet2 is updated | Excel Programming | |||
how do copy "sheet1!A1+1 in sheet2 to sheet 3 and get "sheet2!A1+ | Excel Discussion (Misc queries) | |||
[=Sheet1!A1] - if i use Column A1 ('Sheet1') / A2 ('Sheet2') | Excel Worksheet Functions | |||
go to sheet1 to sheet2 | Excel Programming |