Runtime Error 9 - Subscript out of range in Print Program on Page Breaks

Viewed 16

I have created a multi-sheet financial file containing 1 sheet for each month. The data on each sheet is exactly the same size and shape (A1 to V186). Each sheet contains 2 print program buttons; one to print a detail report, and the other to print a summary report. The same 2 buttons are on each sheet running the same 2 programs and use variables to get the code name and index number of the active sheet. The summary print uses A4 Portrait, no page breaks and works fine; but the A3 Landscape 2 page breaks in the detail report are a real problem for me to solve. I have spent 2 full days researching and trying all different suggested solutions to get the page breaks to work,(Location, Before, etc etc) and I just can't work it out. Your help would be greatly appreciated.

Sub Print_Month_Detail()
'
' Print_Month_Detail Macro
'

 Dim CShtIdx As Integer
 Dim SheetName As String

 CShtIdx = ActiveSheet.Index
 SheetName = ActiveSheet.CodeName

 Worksheets(CShtIdx).Activate

 ActiveWindow.View = xlPageBreakPreview
 Worksheets(CShtIdx).ResetAllPageBreaks
 Set Worksheets(CShtIdx).HPageBreaks(1).Location = Worksheets(CShtIdx).Range("A70")
 Set Worksheets(CShtIdx).HPageBreaks(2).Location = Worksheets(CShtIdx).Range("A138")
'
 Application.PrintCommunication = False
 ActiveSheet.PageSetup.PrintArea = "$A$1:$V$186"
 With ActiveSheet.PageSetup
     .PrintTitleRows = "$1:$2"
        .PrintTitleColumns = ""

'   ...... more print settings.......


    End With
'
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=True, Collate:=True, IgnorePrintAreas:=False
Range("A1").Select
End Sub
0 Answers
Related