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

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

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
>

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

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
>

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