J
Janis
The line it stops on is on the line it sets the RowsPerPage Variable.
ActiveSheet.DisplayPageBreaks = True
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.ResetAllPageBreaks
'ActiveWindow.View = xlNormalView
x = ActiveSheet.HPageBreaks.Count
RowsPerPage = ActiveSheet.HPageBreaks(2).Location.Row -
ActiveSheet.HPageBreaks(1).Location.Row
The RowsPerPage variable is declared as a long. Is it running out of memory?
I noticed on my CPU usage is around 98%. Do I need a bigger cpu? My
computer is
Intel(R) Core(TM) 2 cpu, [email protected], 2.13 GHz 0.99GB ram
tia,
Janis
------the whole sub------------------------
Sub V_11x17_Page_Setup()
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()
Application.ActivePrinter = "\\bay\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
RowsPerPage = ActiveSheet.HPageBreaks(2).Location.Row -
ActiveSheet.HPageBreaks(1).Location.Row
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
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
------------
ActiveSheet.DisplayPageBreaks = True
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.ResetAllPageBreaks
'ActiveWindow.View = xlNormalView
x = ActiveSheet.HPageBreaks.Count
RowsPerPage = ActiveSheet.HPageBreaks(2).Location.Row -
ActiveSheet.HPageBreaks(1).Location.Row
The RowsPerPage variable is declared as a long. Is it running out of memory?
I noticed on my CPU usage is around 98%. Do I need a bigger cpu? My
computer is
Intel(R) Core(TM) 2 cpu, [email protected], 2.13 GHz 0.99GB ram
tia,
Janis
------the whole sub------------------------
Sub V_11x17_Page_Setup()
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()
Application.ActivePrinter = "\\bay\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
RowsPerPage = ActiveSheet.HPageBreaks(2).Location.Row -
ActiveSheet.HPageBreaks(1).Location.Row
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
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
------------