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

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

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

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

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

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

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