What I'm trying to change a heading on sheet 1 and print the page- the
"variable" heading information is in column A on sheet 3 and the first time
that is comes to a blank - end the loop. If print fine the first time and
then it gives me a Run-time error '13" on the Loop Until line
Am I even close with the logic found below?
Sub print_many()
Dim iRow As Long
iRow = 0
With Range("NameList")
Do
Range("TestName").Value = Sheet3.Range("a" & iRow + 1).Value
Sheet1.PrintOut
iRow = iRow + 1
Loop Until .Offset(iRow, 0).Value = ""
End With
End Sub
Don Guillett - 20 Mar 2008 22:53 GMT
Can't quite tell what's going on here but, assuming your "rangelist" is
defined, why not just something like:
sub printem()
for each c in range("rangelist")
Range("TestName").Value = c
Sheet1.PrintOut
next c
end sub

Signature
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
>
> What I'm trying to change a heading on sheet 1 and print the page- the
[quoted text clipped - 17 lines]
>
> End Sub
Jim Thomlinson - 20 Mar 2008 23:16 GMT
From the code posted I don't think NameList is a dynamic named range. I think
it is a single cell on sheet 3...

Signature
HTH...
Jim Thomlinson
> Can't quite tell what's going on here but, assuming your "rangelist" is
> defined, why not just something like:
[quoted text clipped - 26 lines]
> >
> > End Sub
Jim Thomlinson - 20 Mar 2008 23:14 GMT
There is an inherant paradox in using named ranges in code. A global named
range is visible from any sheet. When you write code however if you have a
line like
Range("NameList")
it is the same as
ActiveSheet.Range("NameList")
if NameList does not exist on the active sheet then the code will fail. So
as a guess your sheet 1 is active when you run this code. Since NameList is
on sheet 3 the code dies.
Take a look at this code to see if it makes sense to you...
Sub PrintMany()
Dim rng As Range
Dim rngToSearch As Range
With Sheet3
Set rngToSearch = .Range(.Range("NameList"), _
.Range("NameList").End(xlDown))
End With
For Each rng In rngToSearch
Sheet1.Range("TestName").Value = rng.Value
Sheet1.PrintPreview
Next rng
End Sub
or with your code...
Sub print_many()
Dim iRow As Long
iRow = 0
With Sheet3.Range("NameList")
Do
Sheet1.Range("TestName").Value = Sheet3.Range("a" & iRow + 1).Value
Sheet1.PrintOut
iRow = iRow + 1
Loop Until .Offset(iRow, 0).Value = ""
End With
End Sub

Signature
HTH...
Jim Thomlinson
> What I'm trying to change a heading on sheet 1 and print the page- the
> "variable" heading information is in column A on sheet 3 and the first time
[quoted text clipped - 15 lines]
>
> End Sub
Brad - 21 Mar 2008 14:44 GMT
I posted a thank you - but don't think that the message stuck.
It took a minute for me to understand rng as a rng and a counter, but the
lightbulb went off (at least I think that it did).
> There is an inherant paradox in using named ranges in code. A global named
> range is visible from any sheet. When you write code however if you have a
[quoted text clipped - 59 lines]
> >
> > End Sub