I lose page break formatting when attempting to print

[mferrisi]

Hello,

This code is in a workbook that creates another workbook called "Attribution_Analysis", which includes the sheet "Attribution." I want to format the page, then give the user the option to print. The page formats, but if I perform any action, such as 'print' or go to 'print preview' I lose me formatting. Is there a way to prevent this? (if I select 'yes' in the message box, the document prints, but without the formatting changes.
Thank you,

Matt


[VBA] Sub Format_Printing()
Dim Rw As Long, Rws As Long, Cols As Long, Sets As Long
Dim i As Long, j As Long, k As Long
Dim Data As Range, Pages As Long
Dim PRange As Range
Dim CRange As Range
Dim MySheet As String

Sheets("Attribution").Select
Columns("D:BH").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.ResetAllPageBreaks

ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
Set ActiveSheet.HPageBreaks(1).Location = Range("A76" )
Set ActiveSheet.HPageBreaks(2).Location = Range("A152")
Set ActiveSheet.HPageBreaks(3).Location = Range("A229")

With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With

Dim Answer As String
Dim Message As String


Message = "Would You Like to Print the Formatted Document?"
'Display MessageBox
Answer = MsgBox(Message, vbQuestion + vbYesNo, "Print?")
If Answer = vbYes Then
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=3, Copies:=1, Collate _
:=True
Else

End If

End Sub[/VBA]

[mdmackillop]

Sorry,
I can't replicate your problem. Can you post your workbook?

[johnske]

Try putting your formatting changes as a BeforePrint event in the ThisWorkbook module...

[VBA]
Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'your formatting changes
End Sub
[/VBA]

[mferrisi]

The worksheet that will not stay formatted is created by a different worksheet, and the formatting is done right at the end, and only formats one sheet from the new workbook. The entire program is much to long to paste here, but basically it pulls data from a database, runs a series of analyses, creates a new workbook, outputs the new data onto one sheet, and formats the sheet for printing.


'************************************************************************** ****
'Formats the pages to 'landscape' and fits them in three pages
'************************************************************************** ****
Call Format_Printing

End Sub

The program runs to completion just fine, with the formatting intact. However, if I click to print, the sheet reverts to its old 10 sheet format, resetting the adjusted pagebreaks and formats. (If I click "print preview" that same thing occurs, or if I go to View -> Normal and then back to -> Page Break Preview, insert cells, or perform any toolbar task.)