How to insert a page break in Excel VBA

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?

Craig wrote:
>
> 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

--

Dave Peterson

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,

- Al

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.

GoFigure wrote:
>
> 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,
>
> - Al
>
> +-------------------------------------------------------------------+
> |Filename: HPageBreaks_Resize Error.bmp |
> |Download: http://www.excelforum.com/attachment.php?postid=4090 |
> +-------------------------------------------------------------------+
>
> --
> GoFigure
> ------------------------------------------------------------------------
> GoFigure's Profile: http://www.excelforum.com/member.php...fo&userid=4274
> View this thread: http://www.excelforum.com/showthread...hreadid=490001

--

Dave Peterson

Thanks, Dave.

Here's what's in that tiny image:

"Run-time error '-13':

Type mismatch"

Thanks for any help.

- Al

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"



GoFigure wrote:
>
> Thanks, Dave.
>
> Here's what's in that tiny image:
>
> "Run-time error '-13':
>
> Type mismatch"
>
> Thanks for any help.
>
> - Al
>
> --
> GoFigure
> ------------------------------------------------------------------------
> GoFigure's Profile: http://www.excelforum.com/member.php...fo&userid=4274
> View this thread: http://www.excelforum.com/showthread...hreadid=490001

--

Dave Peterson

Thanks, Dave.

- Al

This info you sent me Dave, could just get me over the "hump" Thanks!!!

"Dave Peterson" wrote:

> 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?
>
> Craig wrote:
> >
> > 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
>
> --
>
> Dave Peterson
>

Ah, there's always another hump on that horizon.

Craig wrote:
>
> This info you sent me Dave, could just get me over the "hump" Thanks!!!
>