Hello Everyone,

Is there a way to automatically insert page breaks each time the data in
Column A changes? Also, I am bringing the data down from the mainframe, and
the # of rows will change daily. Is there a way to have the print range
automatically update to take into account the change in the # of rows?

Thanks for your help.

Ron

This line of code puts the first page break at row 43.

Set ActiveSheet.HPageBreaks(1).Location = Range("A43")

You could loop through the cells in column A looking for a criteria to
be met (whatever that is for you) and then set that line to be a page
break. Something like:

Dim i

i=1

For each rng in range("A:A")
If rng.value = "Total" then
Set ActiveSheet.HPageBreaks(i).Location = Range("A" & rng.row)
i = i + 1
End if
Next rng

Brian,
Thanks for the quick response, but I keep getting a Subscript out of range
error when I tried this code. Any thoughts?

Also, I am new to VBA, any ideas on how to get it to loop through column A
looking for a change? The spreadsheet lists our warehouse code in column A,
and there is no total line or other type of break when the warehouse code
changes.

Thanks

"Brian Taylor" wrote:

This line of code puts the first page break at row 43.

Set ActiveSheet.HPageBreaks(1).Location = Range("A43")

You could loop through the cells in column A looking for a criteria to
be met (whatever that is for you) and then set that line to be a page
break. Something like:

Dim i

i=1

For each rng in range("A:A")
If rng.value = "Total" then
Set ActiveSheet.HPageBreaks(i).Location = Range("A" & rng.row)
i = i + 1
End if
Next rng

Sorry I didn't realize you had other questions in there. To have your
print range update automtically do a google search for dynamic named
ranges(setting a named range by using the offset formula).

Also if your criteria is where the data in A changes then you would
change my previous example to:

Dim rng as range
Dim i as integer

i=1

For each rng in range("A:A")
If not rng.value = rng.Offset(-1).Value then
Set ActiveSheet.HPageBreaks(i).Location = Range("A" & rng.row)
i = i + 1
End if
Next rng

Brian,
Now I am getting an "Application- defined or Object-defined"error on the
If not rng.value line.
Any thoughts, or am I doing something wrong?

Thanks

"Brian Taylor" wrote:

Sorry I didn't realize you had other questions in there. To have your
print range update automtically do a google search for dynamic named
ranges(setting a named range by using the offset formula).

Also if your criteria is where the data in A changes then you would
change my previous example to:

Dim rng as range
Dim i as integer

i=1

For each rng in range("A:A")
If not rng.value = rng.Offset(-1).Value then
Set ActiveSheet.HPageBreaks(i).Location = Range("A" & rng.row)
i = i + 1
End if
Next rng

Sorry Ron. That was untested, and therefore most likely to be wrong.
Try this:

For Each rng In ActiveSheet.Range("A:A")
If Not rng.Row = 1 Then
If (Not rng.Value = rng.Offset(-1).Value) Then
ActiveSheet.HPageBreaks.Add Befo=Range("A" & rng.Row)
End If
End If
Next rng

That did it.

Thanks for all your help. I really appreciate it.



"Brian Taylor" wrote:

Sorry Ron. That was untested, and therefore most likely to be wrong.
Try this:

For Each rng In ActiveSheet.Range("A:A")
If Not rng.Row = 1 Then
If (Not rng.Value = rng.Offset(-1).Value) Then
ActiveSheet.HPageBreaks.Add Befo=Range("A" & rng.Row)
End If
End If
Next rng