Is it possible to use a formula to put page breaks into a worksheet. ie. Put
a page break when the word TOTAL appears on the spreadsheet?

Hi

a formula can not do this sort of thing. You'll need to use a macro like
the following example:
----
Sub InsertPageBreaks()
ColumnControl = 1 ' column to check for the word total (A=1, B=2 etc -
CHANGE AS NECESSARY)
RowStart = 1 ' starting row

RowStart = RowStart + 1
Do
If Cells(RowStart, ColumnControl).Offset(-1) = "Total" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=Cells(RowStart,
ColumnControl)
Application.StatusBar = "Now adding page break to row " & RowStart
End If
RowStart = RowStart + 1
Loop While Cells(RowStart, ColumnControl) < ""
Application.StatusBar = ""
End Sub
---

to use this code, right mouse click on a sheet tab and choose view code
from the menu choose insert / module
copy & paste the code onto the right hand side of the screen
if any lines go red, click and the end of the line and press the delete
key - this should fix line wrap problems
then use ALT & F11 to switch back to your workbook
choose tools / macro / macros - look for the one that says
"InsertPageBreaks" and press the RUN button

hope this helps
Cheers
JulieD


"fultanio" wrote in message
...
Is it possible to use a formula to put page breaks into a worksheet. ie.
Put
a page break when the word TOTAL appears on the spreadsheet?

That's seems great-tried it but came up invalid character when i tried to run
it?
I put it in exactly like this-is this correct or have I missed something?
Never used a macro before so apologises if I seem stupid!!

Sub InsertPageBreaks()
ColumnControl = 1
RowStart = 1
RowStart = RowStart + 1
Do
If Cells(RowStart, ColumnControl).Offset(-1) = "Total" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=Cells(RowStart,
ColumnControl)
Application.StatusBar = "Now adding page break to row " & RowStart
End If
RowStart = RowStart + 1
Loop While Cells(RowStart, ColumnControl) < ""
Application.StatusBar = ""
End Sub


"JulieD" wrote:

Hi

a formula can not do this sort of thing. You'll need to use a macro like
the following example:
----
Sub InsertPageBreaks()
ColumnControl = 1 ' column to check for the word total (A=1, B=2 etc -
CHANGE AS NECESSARY)
RowStart = 1 ' starting row

RowStart = RowStart + 1
Do
If Cells(RowStart, ColumnControl).Offset(-1) = "Total" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=Cells(RowStart,
ColumnControl)
Application.StatusBar = "Now adding page break to row " & RowStart
End If
RowStart = RowStart + 1
Loop While Cells(RowStart, ColumnControl) < ""
Application.StatusBar = ""
End Sub
---

to use this code, right mouse click on a sheet tab and choose view code
from the menu choose insert / module
copy & paste the code onto the right hand side of the screen
if any lines go red, click and the end of the line and press the delete
key - this should fix line wrap problems
then use ALT & F11 to switch back to your workbook
choose tools / macro / macros - look for the one that says
"InsertPageBreaks" and press the RUN button

hope this helps
Cheers
JulieD


"fultanio" wrote in message
...
Is it possible to use a formula to put page breaks into a worksheet. ie.
Put
a page break when the word TOTAL appears on the spreadsheet?

hi

- there's a first time for everything :)
couple of questions:
are any of the lines red in your code window?
what column do you have the word "total" in?
is it in a cell by itself or a merged one?
how exactly is the word total written in your workbook (e.g. TOTAL, total,
Total)?
whe you run it and it comes up with invalid character - is any line
highlighted?

cheers
JulieD


"fultanio" wrote in message
...
That's seems great-tried it but came up invalid character when i tried to
run
it?
I put it in exactly like this-is this correct or have I missed something?
Never used a macro before so apologises if I seem stupid!!

Sub InsertPageBreaks()
ColumnControl = 1
RowStart = 1
RowStart = RowStart + 1
Do
If Cells(RowStart, ColumnControl).Offset(-1) = "Total" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=Cells(RowStart,
ColumnControl)
Application.StatusBar = "Now adding page break to row " & RowStart
End If
RowStart = RowStart + 1
Loop While Cells(RowStart, ColumnControl) < ""
Application.StatusBar = ""
End Sub


"JulieD" wrote:

Hi

a formula can not do this sort of thing. You'll need to use a macro like
the following example:
----
Sub InsertPageBreaks()
ColumnControl = 1 ' column to check for the word total (A=1, B=2 etc -
CHANGE AS NECESSARY)
RowStart = 1 ' starting row

RowStart = RowStart + 1
Do
If Cells(RowStart, ColumnControl).Offset(-1) = "Total" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=Cells(RowStart,
ColumnControl)
Application.StatusBar = "Now adding page break to row " & RowStart
End If
RowStart = RowStart + 1
Loop While Cells(RowStart, ColumnControl) < ""
Application.StatusBar = ""
End Sub
---

to use this code, right mouse click on a sheet tab and choose view code
from the menu choose insert / module
copy & paste the code onto the right hand side of the screen
if any lines go red, click and the end of the line and press the delete
key - this should fix line wrap problems
then use ALT & F11 to switch back to your workbook
choose tools / macro / macros - look for the one that says
"InsertPageBreaks" and press the RUN button

hope this helps
Cheers
JulieD


"fultanio" wrote in message
...
Is it possible to use a formula to put page breaks into a worksheet.
ie.
Put
a page break when the word TOTAL appears on the spreadsheet?

I am still trying to do this- gave up last time! Its driving me mad!

I have put this in (as below), but nothing happens when I run the macro. I
want a page break every time 'CC TOTAL' shows up on my report. THis is
exactly how it appears in block capitals. It is in the column A, there is no
merge cells in the report, but I have formatted a header to appear on every
page don't know if this makes a difference?

Thanks


Sub InsertPageBreaks()
ColumnControl = 1
RowStart = 1
RowStart = RowStart + 1
Do
If Cells(RowStart, ColumnControl).Offset(-1) = "CC TOTAL" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=Cells(RowStart,
ColumnControl)
Application.StatusBar = "Now adding page break to row " & RowStart
End If
RowStart = RowStart + 1
Loop While Cells(RowStart, ColumnControl) < ""
Application.StatusBar = ""
End Sub



"JulieD" wrote:

hi

- there's a first time for everything :)
couple of questions:
are any of the lines red in your code window?
what column do you have the word "total" in?
is it in a cell by itself or a merged one?
how exactly is the word total written in your workbook (e.g. TOTAL, total,
Total)?
whe you run it and it comes up with invalid character - is any line
highlighted?

cheers
JulieD


"fultanio" wrote in message
...
That's seems great-tried it but came up invalid character when i tried to
run
it?
I put it in exactly like this-is this correct or have I missed something?
Never used a macro before so apologises if I seem stupid!!

Sub InsertPageBreaks()
ColumnControl = 1
RowStart = 1
RowStart = RowStart + 1
Do
If Cells(RowStart, ColumnControl).Offset(-1) = "Total" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=Cells(RowStart,
ColumnControl)
Application.StatusBar = "Now adding page break to row " & RowStart
End If
RowStart = RowStart + 1
Loop While Cells(RowStart, ColumnControl) < ""
Application.StatusBar = ""
End Sub


"JulieD" wrote:

Hi

a formula can not do this sort of thing. You'll need to use a macro like
the following example:
----
Sub InsertPageBreaks()
ColumnControl = 1 ' column to check for the word total (A=1, B=2 etc -
CHANGE AS NECESSARY)
RowStart = 1 ' starting row

RowStart = RowStart + 1
Do
If Cells(RowStart, ColumnControl).Offset(-1) = "Total" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=Cells(RowStart,
ColumnControl)
Application.StatusBar = "Now adding page break to row " & RowStart
End If
RowStart = RowStart + 1
Loop While Cells(RowStart, ColumnControl) < ""
Application.StatusBar = ""
End Sub
---

to use this code, right mouse click on a sheet tab and choose view code
from the menu choose insert / module
copy & paste the code onto the right hand side of the screen
if any lines go red, click and the end of the line and press the delete
key - this should fix line wrap problems
then use ALT & F11 to switch back to your workbook
choose tools / macro / macros - look for the one that says
"InsertPageBreaks" and press the RUN button

hope this helps
Cheers
JulieD


"fultanio" wrote in message
...
Is it possible to use a formula to put page breaks into a worksheet.
ie.
Put
a page break when the word TOTAL appears on the spreadsheet?

I'm still trying to do this- its driving me mad!
I have put in my attempt to the macro (as below), but when I try and run it
nothing seems to happen.
I want a page break every time the phrase 'CC TOTAL' appears in column A,
this is exactly as it appears in the report.
There is no merged cells, but I have formatted a header to appear on every
page if this makes a difference?

Thanks
Marco

Sub InsertPageBreaks()
ColumnControl = 1
RowStart = 1
RowStart = RowStart + 1
Do
If Cells(RowStart, ColumnControl).Offset(-1) = "CC TOTAL" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=Cells(RowStart,
ColumnControl)
Application.StatusBar = "Now adding page break to row " & RowStart
End If
RowStart = RowStart + 1
Loop While Cells(RowStart, ColumnControl) < ""
Application.StatusBar = ""
End Sub


"JulieD" wrote:

hi

- there's a first time for everything :)
couple of questions:
are any of the lines red in your code window?
what column do you have the word "total" in?
is it in a cell by itself or a merged one?
how exactly is the word total written in your workbook (e.g. TOTAL, total,
Total)?
whe you run it and it comes up with invalid character - is any line
highlighted?

cheers
JulieD


"fultanio" wrote in message
...
That's seems great-tried it but came up invalid character when i tried to
run
it?
I put it in exactly like this-is this correct or have I missed something?
Never used a macro before so apologises if I seem stupid!!

Sub InsertPageBreaks()
ColumnControl = 1
RowStart = 1
RowStart = RowStart + 1
Do
If Cells(RowStart, ColumnControl).Offset(-1) = "Total" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=Cells(RowStart,
ColumnControl)
Application.StatusBar = "Now adding page break to row " & RowStart
End If
RowStart = RowStart + 1
Loop While Cells(RowStart, ColumnControl) < ""
Application.StatusBar = ""
End Sub


"JulieD" wrote:

Hi

a formula can not do this sort of thing. You'll need to use a macro like
the following example:
----
Sub InsertPageBreaks()
ColumnControl = 1 ' column to check for the word total (A=1, B=2 etc -
CHANGE AS NECESSARY)
RowStart = 1 ' starting row

RowStart = RowStart + 1
Do
If Cells(RowStart, ColumnControl).Offset(-1) = "Total" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=Cells(RowStart,
ColumnControl)
Application.StatusBar = "Now adding page break to row " & RowStart
End If
RowStart = RowStart + 1
Loop While Cells(RowStart, ColumnControl) < ""
Application.StatusBar = ""
End Sub
---

to use this code, right mouse click on a sheet tab and choose view code
from the menu choose insert / module
copy & paste the code onto the right hand side of the screen
if any lines go red, click and the end of the line and press the delete
key - this should fix line wrap problems
then use ALT & F11 to switch back to your workbook
choose tools / macro / macros - look for the one that says
"InsertPageBreaks" and press the RUN button

hope this helps
Cheers
JulieD


"fultanio" wrote in message
...
Is it possible to use a formula to put page breaks into a worksheet.
ie.
Put
a page break when the word TOTAL appears on the spreadsheet?