K
Kiba
So I need to set a vertical page break and this is the macro I'm running but
it errors at this line.
Set ActiveSheet.VPageBreaks(1).Location = Range("S1")
Here's the full macro
_______________________________________________________________
Sub Test()
Set ActiveSheet.VPageBreaks(1).Location = Range("S1")
Columns("S:S").EntireColumn.AutoFit
ActiveWindow.ScrollColumn = 6
Columns("T:T").EntireColumn.AutoFit
Columns("U:U").EntireColumn.AutoFit
Columns("V:V").EntireColumn.AutoFit
Columns("X:X").Select
Columns("W:W").EntireColumn.AutoFit
Columns("X:X").EntireColumn.AutoFit
Range("T3").Select
ActiveCell.FormulaR1C1 = "=SUM(K:K)"
Range("T3:X3").Select
Selection.FillRight
Range("K1:O2").Select
Selection.Copy
Range("T1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("S3").Select
ActiveCell.FormulaR1C1 = "Tarkett Totals"
With ActiveCell.Characters(Start:=1, Length:=14).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("S1:X3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub
it errors at this line.
Set ActiveSheet.VPageBreaks(1).Location = Range("S1")
Here's the full macro
_______________________________________________________________
Sub Test()
Set ActiveSheet.VPageBreaks(1).Location = Range("S1")
Columns("S:S").EntireColumn.AutoFit
ActiveWindow.ScrollColumn = 6
Columns("T:T").EntireColumn.AutoFit
Columns("U:U").EntireColumn.AutoFit
Columns("V:V").EntireColumn.AutoFit
Columns("X:X").Select
Columns("W:W").EntireColumn.AutoFit
Columns("X:X").EntireColumn.AutoFit
Range("T3").Select
ActiveCell.FormulaR1C1 = "=SUM(K:K)"
Range("T3:X3").Select
Selection.FillRight
Range("K1:O2").Select
Selection.Copy
Range("T1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("S3").Select
ActiveCell.FormulaR1C1 = "Tarkett Totals"
With ActiveCell.Characters(Start:=1, Length:=14).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("S1:X3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub