VBA Help - Setup where to insert Page break - Excel 2010

[Johnny Thunder]

Hi guys,

Stuck on a code and can't seem to get it to work, hoping someone has come across this in the past or knows how to do this.

I have a Set Print Area script that loops thru sheet in a newly added workbook and sets print area and some formatting and I added a line that inserts a page break on row 66 but this doesn't seem to be working. Currently it is entering the line break on row 90.

Any help is appreciated.

Sub PrintArea()


Dim lr As Long
Dim ws As Worksheet, sht As Worksheet
Dim cL As Variant 'Finds Column Letter


Application.ScreenUpdating = False
Application.DisplayAlerts = False


For Each ws In ActiveWorkbook.Worksheets
  
  Select Case ws.Name
        Case "Lookups"
            'For sheets you don't want affected by the macro create a case like the "lookups Sheet line
        Case Else
    
    With ws
 cL = Split(ws.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Cells.Address(1, 0), "$")(0) 'Finds last column with Data
 lr = .Range("B" & Rows.Count).End(xlUp).Row 'Finds last row with data
   
 'Setup for the Print Area and Format of Margins
.PageSetup.PrintArea = .Range("B1:" & cL & lr).Address
.PageSetup.Zoom = False
.PageSetup.LeftMargin = Application.InchesToPoints(0.5)
.PageSetup.RightMargin = Application.InchesToPoints(0.5)
.PageSetup.TopMargin = Application.InchesToPoints(0.5)
.PageSetup.BottomMargin = Application.InchesToPoints(0.5)
.PageSetup.HeaderMargin = Application.InchesToPoints(0.2)
.PageSetup.FooterMargin = Application.InchesToPoints(0.2)
.PageSetup.FitToPagesWide = 1
.PageSetup.FitToPagesTall = 2
.PageSetup.Orientation = xlLandscape 'or xlPortrait
.ResetAllPageBreaks
.HPageBreaks.Add Before:=Rows(66) 'Page break line
 
    
End With
  
  End Select


Next ws


End Sub

 

[Johnny Thunder]

Ok, got a bit further and got the page break to drop the line break manually at row 66 but will need to dynamically set the Set print area part so that the line break looks normal.


I am now getting an Invalid Qualifier at the line in the code below, any help is appreciated. I am almost there!

Here is the updated code:

Sub PrintAreaByACT()


Dim lr As Long
Dim FindRow As Long, FindRNumber As Long
Dim ws As Worksheet
Dim cL As Variant 'Finds Column Letter


Application.ScreenUpdating = False
Application.DisplayAlerts = False


Set ws = ActiveSheet
   
 With ws
 
 cL = Split(ws.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Cells.Address(1, 0), "$")(0) 'Finds last column with Data
 lr = .Range("B" & Rows.Count).End(xlUp).Row 'Finds last row with data
 
 FindRow = .Range("C:C").Find(What:="Total Expense", LookIn:=xlValues) 'Searches for text in Column C
 
 MsgBox FindRow
 
 FindRNumber = FindRow.Row 'Defines row for found text    '<----------------------Getting the Invalid Qualifier error on this line
  
   
ActiveWindow.View = xlPageBreakPreview
    ActiveSheet.ResetAllPageBreaks
       Set ActiveSheet.HPageBreaks(1).Location = Range("B66")
           ActiveWindow.View = xlNormalView
   
   
 'Setup for the Print Area and Format of Margins
.PageSetup.PrintArea = .Range("B1:" & cL & FindRNumber).Address
.PageSetup.Zoom = False
.PageSetup.LeftMargin = Application.InchesToPoints(0.5)
.PageSetup.RightMargin = Application.InchesToPoints(0.5)
.PageSetup.TopMargin = Application.InchesToPoints(0.5)
.PageSetup.BottomMargin = Application.InchesToPoints(0.5)
.PageSetup.HeaderMargin = Application.InchesToPoints(0.2)
.PageSetup.FooterMargin = Application.InchesToPoints(0.2)
.PageSetup.FitToPagesWide = 1
.PageSetup.FitToPagesTall = 2
.PageSetup.Orientation = xlLandscape 'or xlPortrait
   
End With


End Sub