Experimenting with (offset in VBA)
 

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

Experimenting with (offset in VBA)
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Brad" wrote in message
...

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

Experimenting with (offset in VBA)
 

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

Jim Thomlinson


"Brad" wrote:


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

Experimenting with (offset in VBA)
 

From the code posted I don't think NameList is a dynamic named range. I think
it is a single cell on sheet 3...
--
HTH...

Jim Thomlinson


"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Brad" wrote in message
...

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

Experimenting with (offset in VBA)
 

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

"Jim Thomlinson" wrote:

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

Jim Thomlinson


"Brad" wrote:


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