Setting Prnt Areas & Page Breaks

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

Randy Numbers

Hi All,

Here's the challenge:
I have a master sheet of questions, with different subsets to be answered
based upon the type of sale. The questions are interspersed. so there are
not clean blocks for each type. I can control the questions being show using
a code and a macro, which hides the non-relevant rows.

Issue:
I want to be able to cleanly print the various sets. Currently, when I have
the page breaks set for the largest group, that group looks fine, but the
other groups come out sometimes with just a few lines per page.

I figure a macro and setting print area and inserting page breaks
automatically is the answer -- but I don't have a clue how to do it.

Many thanks! .../Randy
 
Setting the print area is easy:

ActiveSheet.PageSetup.PrintArea = "A1:J1000"

You can add pagebreaks using the hpagebreaks.add method:

Cells.PageBreak = xlPageBreakNone
With Worksheets("Sheet1")
.HPageBreaks.Add .Range("A32")
End With
 
Hellp!

I'm getting close to what Ineed... now its a matter of first clearing all
pagebreak, then resetting them.

I am using 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:=Worksheets("background").Range("a1").Value

Cells.Select
Cells.PageBreak = xlPageBreakNone
....

but this doesn't reset the old breaks.. Thanks.../R
 
Cells, when unqualified, defaults to the active sheet - you need to
qualify it with the worksheet name. There's also no need to select the
cells:


With Sheets("background")
.UnProtect Password:=.Range("A1").Value
.Cells.PageBreak = xlPageBreakNone
End With


Note, this will only remove manual pagebreaks - automatic pagebreaks
can't be removed (unless you set zoom to "Fit to 1 page(s) wide and 1
page(s) tall)
 
okay... getting closer -- thanks again for you help!

1. I made the changes suggested (set PageSetup to 1x1, copied the code), and
still get Runtime Error 1004: Unable to set PageBreak Property of the Range
Class
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.

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.

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 is using it
as 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
 
Back
Top