I'm writing a VBA program that extracts data from an AutoCAD drawing and
imports it into excel. The worksheet is then printed onto sheets of
labels. There may be as many as 10 sheets of labels in a worksheet.
There are various printers throughout the facility that can be used to
print the labels.

Instead of trying to write code that formats the worksheets for the
particular active printer (the settings for every printer are a little
different), I decided to insert page breaks.
Unfortunately, the hpagebreak command doesn't seem to do anything. It
appears as though the default Excel settings override the VBA page
break functions.

If I adjust the margins, the page breaks follow the margins, but not
what is specified with the hpagebreak code.

How can I use the hpagebreak function to force a page break in a
specific row?

I need to do this in order to get all of the lines of text to appear
properly on each sheet of labels.


--
btm
------------------------------------------------------------------------
btm's Profile: http://www.excelforum.com/member.php...o&userid=24880
View this thread: http://www.excelforum.com/showthread...hreadid=384229

btm,
As you did not include any code, have you tried:

ActiveSheet.HPageBreaks.Add .Range("LastRow").Offset(1, 0)

NickHK

"btm" wrote in message
...

I'm writing a VBA program that extracts data from an AutoCAD drawing and
imports it into excel. The worksheet is then printed onto sheets of
labels. There may be as many as 10 sheets of labels in a worksheet.
There are various printers throughout the facility that can be used to
print the labels.

Instead of trying to write code that formats the worksheets for the
particular active printer (the settings for every printer are a little
different), I decided to insert page breaks.
Unfortunately, the hpagebreak command doesn't seem to do anything. It
appears as though the default Excel settings override the VBA page
break functions.

If I adjust the margins, the page breaks follow the margins, but not
what is specified with the hpagebreak code.

How can I use the hpagebreak function to force a page break in a
specific row?

I need to do this in order to get all of the lines of text to appear
properly on each sheet of labels.


--
btm
------------------------------------------------------------------------
btm's Profile:
http://www.excelforum.com/member.php...o&userid=24880
View this thread: http://www.excelforum.com/showthread...hreadid=384229

NickHK,

I tried that line of code but I get an error message "Expected: =".

Below is a simplified example of what I'm trying to do. I want to ad
pagebreaks every 35 rows.


Sub hpb()
hpbCnt = 0
hpbrow = 0
Worksheets(1).PageSetup.PrintArea = "$A$1:$D$500"
Dim cell As String
Do While hpbCnt < 500
hpbrow = hpbrow + 35
hpbCnt = hpbCnt + 1
cell = "d" & CStr(hpbrow)
Worksheets(1).HPageBreaks(1).Location = Worksheets(1).Range(cell)
Loop

End Sub



bt

--
bt
-----------------------------------------------------------------------
btm's Profile: http://www.excelforum.com/member.php...fo&userid=2488
View this thread: http://www.excelforum.com/showthread.php?threadid=38422

I copied that code and it worked ok for me.

What was the line that caused the error?

btm wrote:

NickHK,

I tried that line of code but I get an error message "Expected: =".

Below is a simplified example of what I'm trying to do. I want to add
pagebreaks every 35 rows.

Sub hpb()
hpbCnt = 0
hpbrow = 0
Worksheets(1).PageSetup.PrintArea = "$A$1:$D$500"
Dim cell As String
Do While hpbCnt < 500
hpbrow = hpbrow + 35
hpbCnt = hpbCnt + 1
cell = "d" & CStr(hpbrow)
Worksheets(1).HPageBreaks(1).Location = Worksheets(1).Range(cell)
Loop

End Sub

btm

--
btm
------------------------------------------------------------------------
btm's Profile: http://www.excelforum.com/member.php...o&userid=24880
View this thread: http://www.excelforum.com/showthread...hreadid=384229

--

Dave Peterson

I should have said that the code didn't cause an error. It didn't actually do
what you wanted.

But this may help:

Option Explicit
Sub hpb2()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

With Worksheets(1)
.ResetAllPageBreaks
FirstRow = 36
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow Step 35
.HPageBreaks.Add Befo=.Cells(iRow, "A")
Next iRow
End With
End Sub



Dave Peterson wrote:

I copied that code and it worked ok for me.

What was the line that caused the error?

btm wrote:

NickHK,

I tried that line of code but I get an error message "Expected: =".

Below is a simplified example of what I'm trying to do. I want to add
pagebreaks every 35 rows.

Sub hpb()
hpbCnt = 0
hpbrow = 0
Worksheets(1).PageSetup.PrintArea = "$A$1:$D$500"
Dim cell As String
Do While hpbCnt < 500
hpbrow = hpbrow + 35
hpbCnt = hpbCnt + 1
cell = "d" & CStr(hpbrow)
Worksheets(1).HPageBreaks(1).Location = Worksheets(1).Range(cell)
Loop

End Sub

btm

--
btm
------------------------------------------------------------------------
btm's Profile: http://www.excelforum.com/member.php...o&userid=24880
View this thread: http://www.excelforum.com/showthread...hreadid=384229

--

Dave Peterson

--

Dave Peterson

btm wrote

Below is a simplified example of what I'm trying to do. I want to add
pagebreaks every 35 rows.

Have a look he http://www.rondebruin.nl/print.htm#Breaks

--
David

btm,
I was somewhat surprised that your code works, given that it appears you are
moving a single HPageBreak on the worksheet, as HPageBreak.Count always=1.
Your loop is out though, as you are loping for 500 HPageBreaks, not the
first 500 cells:
Do While hpbrow < 500 ?

NickHK

"btm" wrote in message
...

NickHK,

I tried that line of code but I get an error message "Expected: =".

Below is a simplified example of what I'm trying to do. I want to add
pagebreaks every 35 rows.


Sub hpb()
hpbCnt = 0
hpbrow = 0
Worksheets(1).PageSetup.PrintArea = "$A$1:$D$500"
Dim cell As String
Do While hpbCnt < 500
hpbrow = hpbrow + 35
hpbCnt = hpbCnt + 1
cell = "d" & CStr(hpbrow)
Worksheets(1).HPageBreaks(1).Location = Worksheets(1).Range(cell)
Loop

End Sub



btm


--
btm
------------------------------------------------------------------------
btm's Profile:
http://www.excelforum.com/member.php...o&userid=24880
View this thread: http://www.excelforum.com/showthread...hreadid=384229