Excel VBA - Set page breaks based on cell content | Excel forum

Resolved · High Priority · Version 2010

Mon 27 Feb 2017, 15:44 Delegate Tania said...

Tania has attended:
Excel VBA Introduction course
Excel VBA Intermediate course

Excel VBA - Set page breaks based on cell content

I'm having trouble developing vba code to set page breaks where cells just outside the print area contain 'PB'.

I've got an excel sheet, with some general information in the first 23 rows, then comes a table (with a filter) which picks out information from a database depending on criteria selected in row 1. The worksheet essentially produces an attendee list based on group and date selected.
There are two sections to the table, the first one for members, the second for visitors.
I'm using the filter to hide the blank cells for both the member and the visitor sections.
I can't use repeat rows to repeat the column heading, so I've had to add the column headings manually at intervals.
When I filter on non blank rows, because of the manual page breaks (and they have to be set at specific points), the hidden rows produce a blank page.

This is what I've tried to put together (based on vba forums and trying to tweak it) - but something isn't working. The page break 'flag's are in column O (with the print area extending to column N.

Sub InsertPageBreaksIfValuePB()
Dim rangeSelection As Range
Dim cellCurrent As Range

Set rangeSelection = Range("O31,O58,O79")
ActiveSheet.ResetAllPageBreaks

For Each cellCurrent In rangeSelection
If (cellCurrent.Value = "PB") Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=ActiveCell

End If

Next cellCurrent
End Sub

Many thanks,
Tania

Wed 1 Mar 2017, 12:17 Trainer Jens said...

RE: Excel VBA - Set page breaks based on cell content

Hi Tania,

Thank you for the forum question.

I will need to have a look at your file to understand, how to solve the issue. Page breaks and filters together can be complicated mix to control from VBA.

If you want me to have a look at your file, please forward the file to:

info@stl-training.co.uk

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Thu 2 Mar 2017, 13:39 Delegate Tania said...

RE: Excel VBA - Set page breaks based on cell content

Jens, I can send you a stripped down workbook with the relevant sheets. Please can you confirm that you will treat the information as confidential.

Many thanks,

Tania

Thu 2 Mar 2017, 13:49 Trainer Jens said...

RE: Excel VBA - Set page breaks based on cell content

Hi Tania,

Yes that will be fine and of course I will treat the information as confidential. We receive many files from clients and all will be treated as confidential files and they will be deleted straight afterwards.

Specially when it is complicated vba there is so many solutions and it is not possible to guide our clients in the right direction, without the file.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Wed 8 Mar 2017, 11:00 Trainer Jens said...

RE: Excel VBA - Set page breaks based on cell content

Hi Tania,

I am sorry that you have had to wait for an answer.

If I understand you right the code below should do the job. Please let me know.

When I get your reply I will immediately delete your file.

Sub InsertPageBreaksIfValuePB()

Dim aRange As Range
Dim RowNumber As Long
Dim rangeSelection As Range
Dim cellCurrent As Range

Set aRange = Range("a25:a10000")
Set rangeSelection = Range("o1:o10000")

ActiveSheet.ResetAllPageBreaks
ActiveSheet.PageSetup.PrintArea = ""

For Each cellCurrent In rangeSelection
If (cellCurrent.Value = "PB") Then
ActiveSheet.Rows(cellCurrent.Row).PageBreak = xlPageBreakManual

End If

Next cellCurrent
For Each cellCurrent In aRange
If cellCurrent = "N" Then
RowNumber = cellCurrent.Row
GoTo continue
End If
Next cellCurrent

continue:
Range("b2", "N" & RowNumber - 1).Name = "PrintAreaRange"
ActiveSheet.PageSetup.PrintArea = "PrintAreaRange"

End Sub

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Wed 8 Mar 2017, 15:17 Delegate Tania said...

RE: Excel VBA - Set page breaks based on cell content

Jens,

Almost there. At the end I need to fix the print area to

ActiveSheet.PageSetup.PrintArea = "$B$2:$N$112"

Your code currently looks at the values in column A, but it doesn't need to do that. If i read it correctly it's looking for the 'N' value, but there will be non N values in a subsequent section.

Many thanks for your help.

Kind regards,
Tania

Fri 10 Mar 2017, 13:54 Delegate Tania said...

RE: Excel VBA - Set page breaks based on cell content

Jens,
I didn't spot that this had been marked as resolved. I've almost got this to work, but I'm having trouble fixing the print area as in my reply above.
Many thanks,
Tania

Fri 17 Mar 2017, 13:40 Trainer Jens said...

RE: Excel VBA - Set page breaks based on cell content

Hi Tania,

I hope the code below can do what you need.

I have to use the "N" in column A to define where your ranges ends otherwise it is not possible. The code below will pick up the ranges you want to have in the print area.

Sub InsertPageBreaksIfValuePB()

Dim aRange As Range
Dim aRange2 As Range
Dim RowNumber As Long
Dim RowNumber2 As Long

Dim rangeSelection As Range
Dim cellCurrent As Range

Set aRange = Range("a25:a10000")
Set aRange2 = Range("a81:a10000")
Set rangeSelection = Range("o1:o10000")

ActiveSheet.ResetAllPageBreaks
ActiveSheet.PageSetup.PrintArea = ""

For Each cellCurrent In rangeSelection
If (cellCurrent.Value = "PB") Then
ActiveSheet.Rows(cellCurrent.Row).PageBreak = xlPageBreakManual

End If

Next cellCurrent
For Each cellCurrent In aRange
If cellCurrent = "N" Then
RowNumber = cellCurrent.Row
GoTo continue
End If
Next cellCurrent

continue:
For Each cellCurrent In aRange2
If cellCurrent = "N" Then
RowNumber2 = cellCurrent.Row
GoTo continue2
End If
Next cellCurrent

continue2:

Union(Range("b2", "N" & RowNumber - 1), Range("b81", "N" & RowNumber2 - 1)).Name = "PrintAreaRange"

'Range("b2", "N" & RowNumber - 1).Name = "PrintAreaRange"
ActiveSheet.PageSetup.PrintArea = "PrintAreaRange"

End Sub

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector