mferrisi
03-23-2007, 06:25 AM
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
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
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
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