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
|