Padawan018
Board Regular
- Joined
- Sep 29, 2006
- Messages
- 63
Hi,
I have a pivot table that has data for years 2007 and 2008. I want set the print area to capture the entire pivot table and set a horizontal page break between the 2007 and 2008 data.
Here is my code but it doesn't seem to be setting the Horizontal Page Break.
The Layout for the Pivot Table is :
Year - Title - Group | Data
Thanks in Advance for your help,
Padawan018
I have a pivot table that has data for years 2007 and 2008. I want set the print area to capture the entire pivot table and set a horizontal page break between the 2007 and 2008 data.
Here is my code but it doesn't seem to be setting the Horizontal Page Break.
Code:
Sub printmenu()
Dim PrintRange
Dim Page2Break
ans = MsgBox("Your printer is currently defaulted to " & vbNewLine & vbNewLine & _
Application.ActivePrinter & vbNewLine & vbNewLine & _
"To select another printer, go to FILE in the Menu Bar, select PRINT." & vbNewLine & _
"Do you want to print this sheet? ", vbYesNo + vbQuestion, Title:="Printer Select")
If ans = vbNo Then Exit Sub
LastRow = Range("B65536").End(xlUp).Row
Range("B7").End(xlDown).Select
Selection.End(xlDown).Select
Set Page2Break = ActiveCell.Offset(0, -1)
PrintRange = Range("B7", Cells(LastRow, "K")).Address
ActiveSheet.ResetAllPageBreaks
ActiveSheet.PageSetup.Printarea = Cells.Address
With ActiveSheet.PageSetup
.PrintTitleRows = "$7:$7"
.Orientation = xlLandscape
.Printarea = PrintRange
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Page2Break
Sheet7.PrintOut
End Sub
The Layout for the Pivot Table is :
Year - Title - Group | Data
Thanks in Advance for your help,
Padawan018