ActiveSheet.name not returning Sheet1, Sheet2

R

Rebecca_SUNY

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
 
D

Dave Peterson

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
 
R

Rebecca_SUNY

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...?
 
D

Dave Peterson

That's what I would assume, too <vbg>.

But it wouldn't take much to test it to confirm or reject that assumption!
 
R

Rebecca_SUNY

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.
 
D

Dave Peterson

Doesn't VBA's help state that it's not case sensitive if "option compare text"
is used?

Rebecca_SUNY said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top