98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel VBA - Set page breaks based on cell content | Excel forum
Excel VBA - Set page breaks based on cell content | Excel forum
Resolved · High Priority · Version 2010
Mon 27 Feb 2017, 15:44Delegate 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:17Trainer 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:39Delegate 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:49Trainer 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:00Trainer 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:17Delegate 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:54Delegate 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:40Trainer 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
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:3D formulas find and replaceAfter you know all the components of a 3D reference, you can change them to suit by using a localised Find and replace crt+f, if need be. |
