Whyd Doesn't This Work?

  • Thread starter Thread starter Randy Numbers
  • Start date Start date
R

Randy Numbers

First, thanks to all who have leped so far... I'm getting closer -- thanks
again for you help!

Here's the full story: I have certain lines I hide, depending upon the use
(new customer, existing, etc.). I want to give the sales admin control over
setting page breaks for the different types. I decided to put an "X" in the
row where a page break is to occur. The macro should read the appropriate
column, and insert the breaks as needed.

1. I made the changes suggested (set PageSetup to 1x1), and
still get Runtime Error 1004: Unable to set PageBreak Property of the Range
Class whedn it gets to { .Cells.PageBreak = xlPageBreakNone}

2. Also, with the setting at 1x1, I can't even insert MANUAL page breaks
from the INSERT menu; only when I change the settings to an auto percentage
will it recognize the manual breaks I put in.


Questions:
1. See the code below -- it occurs to me the ROWNDX counter is relative
(i.e., only counting the VISIBLE rows), while my insert command expects an
absolute row number -- range("A"&ROWNDX). Is this correect? Do I need to
UNHIDE all first?
2. Any suggestions on how to accomplish this?


Sub PRINT_BGROUND()
' Uses columns f - i to set where page breaks go.
'
Dim RowNdx As Long
Dim LastRow As Long

Application.ScreenUpdating = False
With Sheets("background")
.Unprotect Password:=Range("a1").Value

.Cells.PageBreak = xlPageBreakNone
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For RowNdx = LastRow To 8 Step -1

If Range("addon_flag").Value Then
If (.Cells(RowNdx, "h").Value) = "X" _
Then
.HPageBreaks.Add .Range("A" & RowNdx)
End If
End If

If Range("XE_flag").Value Then
If (.Cells(RowNdx, "j").Value) = "X" _
Then
.HPageBreaks.Add .Range("A" & RowNdx)
End If
End If

If Range("new_flag").Value Then
If (.Cells(RowNdx, "i").Value) = "X" _
Then
.HPageBreaks.Add .Range("A" & RowNdx)
End If
End If
Next RowNdx
.Protect Password:=Worksheets("background").Range("a1").Value
Sheets("background").PrintOut Copies:=1, Collate:=True
End With
End Sub
 
You can't both have no page breaks (i.e., set page setup to 1 page wide
by 1 page tall) and have manual page breaks.
 
Back
Top