How to insert a page break in Excel VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I want to insert a page break after a condition has been met. I created a
recorded macro to see what the code looks like then I inserted it into my
real code but got an error

Here is a snippet of the real code:
=================================
If recordCounter = 8 Then

ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
'Sheets("output").Cells(rowNum, colNum).Value="TEXT TEST"
recordCounter = 0
End If
===========================================

I know the rest of the code except for the HPageBreak statement is good
since I tested it before pasting the statement in...I tested it using the
"TEXT TEST" and it did insert sample text..but instead of inserting sample
text at Cell location (rownum, colNum) I want to insert a page break...

Any suggestions...thanks much for any help with this!!!

Craig
 
If your activecell is in row 1, you're going to have trouble.

Where is that activecell?

if activecell.row = 1 then
'do nothing
else
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
end if

And the next question is: Are you sure you want to be depending on the
activecell?
 
Seems like the same topic ...

Why would this statement

ActiveSheet.HPageBreaks.Add Before:="B53"

generate a the run-time error shown in the screenshot?

Thanks,

- A

+-------------------------------------------------------------------
|Filename: HPageBreaks_Resize Error.bmp
|Download: http://www.excelforum.com/attachment.php?postid=4090
+-------------------------------------------------------------------
 
You may want to type that run time error into your message. Lots of people
don't go through excelforum and can't see your screen image.
 
Thanks, Dave.

Here's what's in that tiny image:

"Run-time error '-13':

Type mismatch"

Thanks for any help.

- A
 
Ahh. I should have looked more closely:

ActiveSheet.HPageBreaks.Add Before:=ActiveSheet.Range("B53")

when you did this:
ActiveSheet.HPageBreaks.Add Before:="B53"

That "b35" doesn't mean anything more than a string to excel. It would be like:

ActiveSheet.HPageBreaks.Add Before:="goFigure"
 
Back
Top