For- Next with named ranges

[Graham]

I have a multisheet workbook where I want to go through each of the
worksheets and print out a fixed range in each worksheet if cell C2 is > 0.
I have highlighted areas which will be "unhighlighted" for printing and then
returned to their original state after printing. (thanks to help from Dave
Peterson). These latter highlighted areas are named with the worksheet name
that they reside in. My attempt is shown below.

Sub printmargins()
Dim myRng As Range
Dim myColorIndex As Long
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
If Range("C2") > 0 Then
Set myRng = ActiveSheet.Range("Highlight")
myColorIndex = myRng(1).Interior.ColorIndex
myRng.Interior.ColorIndex = xlNone
sh.Range("a1:d20").PrintOut
myRng.Interior.ColorIndex = myColorIndex
End If
Next
End Sub
My problem is that with each sheet (these are not named Sheet1 Sheet2 etc),
the highlighted area name will change with the name of the sheet. ie set
myRng=ActiveSheet.Range("Sheetname") etc and quite simply I do not know how
to show this, and after that even if this will then work. Grateful for any
guidance or criticism.

Kind Regards,
Graham Haughs
Turriff, Scotland

 

[JE McGimpsey]

One way:

I assume that the "If Range("C2") > 0" refers to each sheet, not always
the activesheet, right? in that case you need to qualify it:

Public Sub printmargins()
Dim myRng As Range
Dim myColorIndex As Long
Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets
With sh
If .Range("C2") > 0 Then
Set myRng = .Range(.Name)
myColorIndex = myRng(1).Interior.ColorIndex
myRng.Interior.ColorIndex = None
.Range("A120").PrintOut
myRng.Interior.ColorIndex = myColorIndex
End If
End With
Next sh
End Sub

 

[Dave Peterson]

You can actually use the same name in each worksheet. But when you define that
range you have to include the name of the sheet, too.

Sheet1!Highlight
'my sheet2'!highlight

Just type it in the namebox (to the left of the formula bar) and hit enter (with
your range selected).

Or just type it in the Insert|names dialog names in workbook box.

Then you can use that name for each worksheet:

Option Explicit
Sub printmargins()
Dim myRng As Range
Dim myColorIndex As Long
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
If sh.Range("C2").Value > 0 Then
Set myRng = Nothing
On Error Resume Next
Set myRng = sh.Range("Highlight")
On Error GoTo 0
If myRng Is Nothing Then
'do nothing, it didn't have a range named "highlight"
Else
myColorIndex = myRng(1).Interior.ColorIndex
myRng.Interior.ColorIndex = xlNone
sh.Range("a1:d20").PrintOut
myRng.Interior.ColorIndex = myColorIndex
End If
End If
Next sh
End Sub

=====
But if you've already named your ranges after the sheet name, you could modify
the code like:

On Error Resume Next
Set myRng = sh.Range(sh.name)
On Error GoTo 0


I think I'd go back to make all those Highlight names local to the sheet. If
someone changes the sheet name, the other code could easily fail for you.

=====
Notice that I qualified the ranges in your code:

Range("C2").value
became
sh.range("c2").value

If you don't qualify that range, it'll refer to the activesheet (usually).

 

[Graham]

Many thanks for the support. I never realised that the same name could be
used in different sheets, although I should have noticed as I have seen the
named range in some workbooks jump to the appropriate sheet. I also
appreciate being shown the way to do it the alternative way with the sheet
names. Grateful thanks to you yet again.

Graham