|
Page Breaks
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? |
| All times are GMT +1. The time now is 08:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com