VBA Script for Print Setup


Posted by Steve on April 20, 2001 8:24 AM

I typically use the following script to reset print settings on shared worksheets. Once invoked the script takes almost a full minute to complete its function. There has to be a more efficient script but I've yet to find one. Any suggestions?

Sub Print_East_Click()
'
' Print_East Macro
' Macro recorded 03/07/2000 by Steve
'
'
Application.Goto Reference:="Print_East"
Print_Setup
End Sub

Sub Print_Setup()

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$4"
.LeftHeader = ""
.CenterHeader = "Preliminary"
.LeftFooter = "&8&F" & Chr(10) & "&A"
.CenterFooter = "&8&P of &N"
.RightFooter = "&8&D" & Chr(10) & "&T"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.05)
.CenterHorizontally = True
.Orientation = xlLandscape
.FirstPageNumber = xlAutomatic
.FitToPagesWide = 1
.FitToPagesTall = 20
End With
Selection.PrintOut Copies:=1, Collate:=True
Application.Goto Reference:="HomeTarget"
End Sub



Posted by Dave Hawley on April 20, 2001 8:36 AM

Hi steve

Printer setup is horrible slow, this may help a bit.


Sub Print_Setup()
Application.ScreenUpdating = False
With ActiveSheet.PageSetup
.DisplayPageBreaks = False
.PrintTitleRows = "$1:$4"
.LeftHeader = ""
.CenterHeader = "Preliminary"
.LeftFooter = "&8&F" & Chr(10) & "&A"
.CenterFooter = "&8&P of &N"
.RightFooter = "&8&D" & Chr(10) & "&T"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.05)
.CenterHorizontally = True
.Orientation = xlLandscape
.FirstPageNumber = xlAutomatic
.FitToPagesWide = 1
.FitToPagesTall = 20
End With
Application.ScreenUpdating = True
Selection.PrintOut Copies:=1, Collate:=True
Application.Goto Reference:="HomeTarget"
End Sub


Dave
OzGrid Business Applications