Page Setup not working in macro

  • Thread starter Thread starter stuwallace
  • Start date Start date
S

stuwallace

I have a macro which I have recorded and does everything I need it to
except changing the page setup infromation. I receive no errors but it
does not even put the header or change the orientation, Any help would
be much appreciated.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 21/11/2007 by stuartw
'

'

Rows("1:1").Select
Selection.Font.Bold = True
Range("C1").Select
ActiveCell.FormulaR1C1 = "'Date Received"
With ActiveCell.Characters(Start:=1, Length:=13).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("F1").Select
ActiveCell.FormulaR1C1 = "Sales man Number"
With ActiveCell.Characters(Start:=1, Length:=16).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("F1").Select
ActiveCell.FormulaR1C1 = "Salesman Number"
With ActiveCell.Characters(Start:=1, Length:=15).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("K1").Select
ActiveCell.FormulaR1C1 = "Opening Balance"
With ActiveCell.Characters(Start:=1, Length:=15).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("L1").Select
ActiveCell.FormulaR1C1 = "Amount Paid"
With ActiveCell.Characters(Start:=1, Length:=11).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Columns("M:Q").Select
Range("Q1").Activate
Selection.Delete Shift:=xlToLeft
Columns("I:J").Select
Range("J1").Activate
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=-3
ActiveWindow.LargeScroll ToRight:=-1
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").ColumnWidth = 9
Columns("C:C").ColumnWidth = 12.14
Columns("D:D").ColumnWidth = 45
Columns("E:E").ColumnWidth = 9.29
Columns("F:F").ColumnWidth = 27
Columns("H:J").Select
Selection.NumberFormat = "0.00"
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("H1").Select
ActiveCell.FormulaR1C1 = "Opening Balance"
With ActiveCell.Characters(Start:=1, Length:=15).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("I1").Select
ActiveCell.FormulaR1C1 = "Amount Paid"
With ActiveCell.Characters(Start:=1, Length:=11).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("J1").Select
ActiveCell.FormulaR1C1 = "Closing Balance"
With ActiveCell.Characters(Start:=1, Length:=15).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("H1:J1").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("F1").Select
ActiveCell.FormulaR1C1 = "Salesman "
With ActiveCell.Characters(Start:=1, Length:=9).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Columns("I:I").Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Range("A1").Select
Selection.Sort Key1:=Range("E2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Selection.Subtotal GroupBy:=5, Function:=xlSum,
TotalList:=Array(9), _
Replace:=True, PageBreaks:=True, SummaryBelowData:=True
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&""Arial,Bold""&14&UDAILY PAYMENTS REPORT"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page &P"
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(1.5)
.RightMargin = Application.InchesToPoints(1.5)
.TopMargin = Application.InchesToPoints(0.984251968503937)
.BottomMargin = Application.InchesToPoints(0.984251968503937)
.HeaderMargin = Application.InchesToPoints(0.511811023622047)
.FooterMargin = Application.InchesToPoints(0.511811023622047)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 85
End With
ActiveWindow.SelectedSheets.PrintPreview
End Sub


Thanks

Stuart
 
The page set up seems to work on my system, but without setting a print
range, Excel will try to set it for you. That can cause the print preview
display to appear as though it has not captured your page setup instructions.
Check the page info on the status bar at the bottom of the print preview
screen to see which page of how many you are viewing. That will give you an
idea of how Excel has allocated your file for printing.
 
Back
Top