Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
I tried the if block to make sure there is a pagebreak in x. There is a
pagebreak in x because I saw it in the immediate window so the problem isn't that there isn't any pagebreaks although it is a nice error check for that. Sub VOD_11x17_Page_Setup() 'This is the new page set up without column B for sheets + VOD_v2. Dim x As Integer Dim I As Integer Dim K As Integer Dim J As String Dim C As Range Dim PageNumber As Long Dim SubTotalRow As Long Dim Test As Boolean Dim Row1 As Integer Dim AC_Sheet As Worksheet Dim AW As Workbook Dim AW_Name As String Dim UsedRange1 As Range Dim UsedRows1 As Long Dim UsedCol1 As Long Dim SubTotalRows As Variant Dim RowsPerPage As Long Application.ActiveSheet.UsedRange Set AC_Sheet = Application.ActiveSheet Set AW = Application.ActiveWorkbook AW_Name = AW.name Set UsedRange1 = AC_Sheet.UsedRange UsedRows1 = UsedRange1.Rows.Count UsedCol1 = UsedRange1.Columns.Count SubTotalRows = GetSubTotalRows() ' nNetwork = getPrinter(nError) ' If nNetwork = 0 Then ' ' MsgBox "the command to print has an error." ' MsgBox nError ' Else ' Application.ActivePrinter = "\\martinezfs1-bay\Ca-Martinez-94C on Ne0" & nNetwork & ":" ' End If Application.ActivePrinter = "\\martinezfs1-bay\CA-Martinez-94C on Ne02:" PS411x17 Application.ScreenUpdating = False With ActiveSheet.PageSetup .PrintArea = "" .PrintTitleRows = "$1:$11" .PrintTitleColumns = "" .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 99 .PrintErrors = xlPrintErrorsDisplayed End With ActiveSheet.DisplayPageBreaks = True ActiveWindow.View = xlPageBreakPreview ActiveSheet.ResetAllPageBreaks ActiveWindow.View = xlNormalView x = ActiveSheet.HPageBreaks.Count If x 2 Then With ActiveSheet.HPageBreaks RowsPerPage = .Item(2).Location.Row - .Item(1).Location.Row End With End If RowsPerPage = ActiveSheet.HPageBreaks(2).Location.Row - ActiveSheet.HPageBreaks(1).Location.Row ' With ActiveSheet.HPageBreaks ' RowsPerPage = .Item(2).Location.Row - .Item(1).Location.Row ' End With K = 1 PageNumber = 1 Row1 = 0 For I = 0 To UBound(SubTotalRows) SubTotalRow = SubTotalRows(I) If Row1 = 0 Then Row1 = ActiveSheet.HPageBreaks(PageNumber).Location.Row End If If SubTotalRow Row1 Then Set ActiveSheet.HPageBreaks(PageNumber).Location = Cells(SubTotalRows(I - 1) + 1, 1) Row1 = SubTotalRows(I - 1) + RowsPerPage PageNumber = PageNumber + 1 End If ' If SubTotalRow Row1 Then ' ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=Cells(SubTotalRows(I - 1) + 1, 1) ' Row1 = SubTotalRows(I - 1) + RowsPerPage ' PageNumber = PageNumber + 11 ' End If Next I For I = 1 To x If x < ActiveSheet.HPageBreaks.Count Then I = I - (ActiveSheet.HPageBreaks.Count - x) K = I + (ActiveSheet.HPageBreaks.Count - x) x = ActiveSheet.HPageBreaks.Count End If J = ActiveSheet.HPageBreaks(K).Location.Address Row1 = Range(J).Row Set ActiveSheet.HPageBreaks(K).Location = Cells(Row1, 1) K = K + 1 Next I Application.ScreenUpdating = True ActiveWindow.View = xlNormalView Range(FirstDataCell).Activate Range("A1").Activate End Sub do you have any other ideas? thanks, |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
runtime error 9 subscript out of range | Excel Worksheet Functions | |||
runtime error 9 out of range subscript #2 | Excel Programming | |||
runtime error 9 out of range subscript | Excel Programming | |||
Runtime error 9 - Subscript out of Range | Excel Programming | |||
Runtime Error '9' Subscript out of range HELP | Excel Programming |