VBA for page setup with dynamic number of pages tall

[Iron_Man]

Hi folks,

I found on this forum a fantastic piece of code to set the page setup of a particular sheet in my workbook.

Sub Print_Format()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With ActiveSheet.PageSetup
    .PrintTitleRows = "1:1"
    .PrintArea = "A1:I" & LR
    .LeftMargin = Application.InchesToPoints(0.1)
    .RightMargin = Application.InchesToPoints(0.1)
    .TopMargin = Application.InchesToPoints(0.3)
    .BottomMargin = Application.InchesToPoints(0.3)
    .Orientation = xlLandscape
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 10
End With
End Sub

My only problem is that I would like the number of pages tall to be a function of the number of lines (ie 100 lines per pages). Is this doable or do I need to forget about it? I tried by using LR and having a new parameter being LR / 100 but it returned a "application or object defined error".

Thanks in advance to anyone who can help.

IM

 

[Iron_Man]

Got it, just need to set FittoPagesTall to false

 

[Fluff]

Hia
How about this

Sub Print_Format()
Dim LR As Long
Dim PageQty As Integer
LR = Range("A" & Rows.Count).End(xlUp).Row
pt = LR / 100
With ActiveSheet.PageSetup
    .PrintTitleRows = "1:1"
    .PrintArea = "A1:I" & LR
    .LeftMargin = Application.InchesToPoints(0.1)
    .RightMargin = Application.InchesToPoints(0.1)
    .TopMargin = Application.InchesToPoints(0.3)
    .BottomMargin = Application.InchesToPoints(0.3)
    .Orientation = xlLandscape
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = PageQty
End With
End Sub