get application, object error

J

Janis

I get a runtime error 1004, application or object definition on the line with
the asterisks. While debugging this I noticed that it runs through the 5th
iteration of "I" then it stops. This macro does the page setup on each page
of long spreadsheets. There is also a routine that does subtotals before it
runs through the page break and formatting. So excel might be choking.

When it stopped in the watch window the variables are:
x= 12
RowsPerPage = 99
Row1 = 111
SubTotalRows(I - 1) evaluates to 96 [the line that stops]

I noticed one strange thing. If I show the page break preview mode I get
the above error. If I run in regular mode then it stops in the same place
but I get a runtime error 9 instead and I don't get any values.

thanks for any help,
---------------code segment-----------


x = ActiveSheet.HPageBreaks.Count
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)
Debug.Print I
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
 
J

Joel

try changing this section of code

If SubTotalRow > Row1 Then
ActiveWindow.SelectedSheets.HPageBreaks.Add _
Before:=Cells(subtotalrows(i - 1) + 1, 1)
Row1 = subtotalrows(i - 1) + RowsPerPage
PageNumber = PageNumber + 1
End If
 
J

Janis

JOel:
Thanks, I tried it and I still get a runtime 9 error, subscript out of range
although it doesn't give me the other application object error anymore
whether or not I put it in preview mode.

This is a wild guess but could it be the part where it keeps counting the
pagebreaks. Is there a way to set the variable and subtract the remaining
pagebreaks without having to have it keep calculating the remaining
pagebreaks. I don't know why it is able to get to the 5th "I" and then it
hangs. Wouldn't that be out of memory or something?

tia,

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

Joel said:
try changing this section of code

If SubTotalRow > Row1 Then
ActiveWindow.SelectedSheets.HPageBreaks.Add _
Before:=Cells(subtotalrows(i - 1) + 1, 1)
Row1 = subtotalrows(i - 1) + RowsPerPage
PageNumber = PageNumber + 1
End If

Janis said:
I get a runtime error 1004, application or object definition on the line with
the asterisks. While debugging this I noticed that it runs through the 5th
iteration of "I" then it stops. This macro does the page setup on each page
of long spreadsheets. There is also a routine that does subtotals before it
runs through the page break and formatting. So excel might be choking.

When it stopped in the watch window the variables are:
x= 12
RowsPerPage = 99
Row1 = 111
SubTotalRows(I - 1) evaluates to 96 [the line that stops]

I noticed one strange thing. If I show the page break preview mode I get
the above error. If I run in regular mode then it stops in the same place
but I get a runtime error 9 instead and I don't get any values.

thanks for any help,
---------------code segment-----------


x = ActiveSheet.HPageBreaks.Count
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)
Debug.Print I
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
Click to expand...
Click to expand...
 
J

Janis

I tried running it for over an hour. First the old way and then with your
code. It is stopping in the same place with both and I get the runtime
subscript out of range error on this line:
RowsPerPage = .Item(2).Location.Row - .Item(1).Location.Row

Any other ideas? TIA,
Janis
----code----
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


'RowsPerPage = ActiveSheet.HPageBreaks(2).Location.Row -
ActiveSheet.HPageBreaks(1).Location.Row
With ActiveSheet.HPageBreaks 'added by Tomlinson
RowsPerPage = .Item(2).Location.Row - .Item(1).Location.Row
End With
K = 1
PageNumber = 1
Row1 = 0

For I = 0 To UBound(SubTotalRows)
Debug.Print I
SubTotalRow = SubTotalRows(I)
If Row1 = 0 Then
Row1 = ActiveSheet.HPageBreaks(PageNumber).Location.Row
End If

' If SubTotalRow > Row1 Then
' ActiveWindow.SelectedSheets.HPageBreaks.Add
Before:=Cells(SubTotalRows(I - 1) + 1, 1)
' Row1 = SubTotalRows(I - 1) + RowsPerPage
' PageNumber = PageNumber + 1
' End If

If SubTotalRow > Row1 Then 'added by Joel
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

Joel said:
try changing this section of code

If SubTotalRow > Row1 Then
ActiveWindow.SelectedSheets.HPageBreaks.Add _
Before:=Cells(subtotalrows(i - 1) + 1, 1)
Row1 = subtotalrows(i - 1) + RowsPerPage
PageNumber = PageNumber + 1
End If

Janis said:
I get a runtime error 1004, application or object definition on the line with
the asterisks. While debugging this I noticed that it runs through the 5th
iteration of "I" then it stops. This macro does the page setup on each page
of long spreadsheets. There is also a routine that does subtotals before it
runs through the page break and formatting. So excel might be choking.

When it stopped in the watch window the variables are:
x= 12
RowsPerPage = 99
Row1 = 111
SubTotalRows(I - 1) evaluates to 96 [the line that stops]

I noticed one strange thing. If I show the page break preview mode I get
the above error. If I run in regular mode then it stops in the same place
but I get a runtime error 9 instead and I don't get any values.

thanks for any help,
---------------code segment-----------


x = ActiveSheet.HPageBreaks.Count
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)
Debug.Print I
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
Click to expand...
Click to expand...
 
J

Joel

Two comments:
1) You should change variable x inside the for loop where x is the counter.
It is very poor programming pratice.
2) this loop doesn't make any sense. Sit down and re-think wha you are
trying to do. I think the IF statement belong before the FOR Loop.

Janis said:
JOel:
Thanks, I tried it and I still get a runtime 9 error, subscript out of range
although it doesn't give me the other application object error anymore
whether or not I put it in preview mode.

This is a wild guess but could it be the part where it keeps counting the
pagebreaks. Is there a way to set the variable and subtract the remaining
pagebreaks without having to have it keep calculating the remaining
pagebreaks. I don't know why it is able to get to the 5th "I" and then it
hangs. Wouldn't that be out of memory or something?

tia,

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

Joel said:
try changing this section of code

If SubTotalRow > Row1 Then
ActiveWindow.SelectedSheets.HPageBreaks.Add _
Before:=Cells(subtotalrows(i - 1) + 1, 1)
Row1 = subtotalrows(i - 1) + RowsPerPage
PageNumber = PageNumber + 1
End If

Janis said:
I get a runtime error 1004, application or object definition on the line with
the asterisks. While debugging this I noticed that it runs through the 5th
iteration of "I" then it stops. This macro does the page setup on each page
of long spreadsheets. There is also a routine that does subtotals before it
runs through the page break and formatting. So excel might be choking.

When it stopped in the watch window the variables are:
x= 12
RowsPerPage = 99
Row1 = 111
SubTotalRows(I - 1) evaluates to 96 [the line that stops]

I noticed one strange thing. If I show the page break preview mode I get
the above error. If I run in regular mode then it stops in the same place
but I get a runtime error 9 instead and I don't get any values.

thanks for any help,
---------------code segment-----------


x = ActiveSheet.HPageBreaks.Count
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)
Debug.Print I
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
Click to expand...
Click to expand...
Click to expand...
 
J

Joel

I only get your error if there are no page breaks on the page. If
pagebreakcount is less than 2 you will get an error because item 1 and/or 2
will not be valid.

Janis said:
I tried running it for over an hour. First the old way and then with your
code. It is stopping in the same place with both and I get the runtime
subscript out of range error on this line:
RowsPerPage = .Item(2).Location.Row - .Item(1).Location.Row

Any other ideas? TIA,
Janis
----code----
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


'RowsPerPage = ActiveSheet.HPageBreaks(2).Location.Row -
ActiveSheet.HPageBreaks(1).Location.Row
With ActiveSheet.HPageBreaks 'added by Tomlinson
RowsPerPage = .Item(2).Location.Row - .Item(1).Location.Row
End With
K = 1
PageNumber = 1
Row1 = 0

For I = 0 To UBound(SubTotalRows)
Debug.Print I
SubTotalRow = SubTotalRows(I)
If Row1 = 0 Then
Row1 = ActiveSheet.HPageBreaks(PageNumber).Location.Row
End If

' If SubTotalRow > Row1 Then
' ActiveWindow.SelectedSheets.HPageBreaks.Add
Before:=Cells(SubTotalRows(I - 1) + 1, 1)
' Row1 = SubTotalRows(I - 1) + RowsPerPage
' PageNumber = PageNumber + 1
' End If

If SubTotalRow > Row1 Then 'added by Joel
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

Joel said:
try changing this section of code

If SubTotalRow > Row1 Then
ActiveWindow.SelectedSheets.HPageBreaks.Add _
Before:=Cells(subtotalrows(i - 1) + 1, 1)
Row1 = subtotalrows(i - 1) + RowsPerPage
PageNumber = PageNumber + 1
End If

Janis said:
I get a runtime error 1004, application or object definition on the line with
the asterisks. While debugging this I noticed that it runs through the 5th
iteration of "I" then it stops. This macro does the page setup on each page
of long spreadsheets. There is also a routine that does subtotals before it
runs through the page break and formatting. So excel might be choking.

When it stopped in the watch window the variables are:
x= 12
RowsPerPage = 99
Row1 = 111
SubTotalRows(I - 1) evaluates to 96 [the line that stops]

I noticed one strange thing. If I show the page break preview mode I get
the above error. If I run in regular mode then it stops in the same place
but I get a runtime error 9 instead and I don't get any values.

thanks for any help,
---------------code segment-----------


x = ActiveSheet.HPageBreaks.Count
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)
Debug.Print I
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
Click to expand...
Click to expand...
Click to expand...
 
J

Janis

JOel
I marked this answered but it still isn't. If you look below I had that x
and the for loop commented out. I accidentally uncommented it when I was
testing it. I should have removed it from the comments but the x and the
for loop aren't in the code and I tested it without it. Sorry it was
confusing but it still stops at the RowsPerPage =. I was careful in testing
it.
Janis

Joel said:
Two comments:
1) You should change variable x inside the for loop where x is the counter.
It is very poor programming pratice.
2) this loop doesn't make any sense. Sit down and re-think wha you are
trying to do. I think the IF statement belong before the FOR Loop.

Janis said:
JOel:
Thanks, I tried it and I still get a runtime 9 error, subscript out of range
although it doesn't give me the other application object error anymore
whether or not I put it in preview mode.

This is a wild guess but could it be the part where it keeps counting the
pagebreaks. Is there a way to set the variable and subtract the remaining
pagebreaks without having to have it keep calculating the remaining
pagebreaks. I don't know why it is able to get to the 5th "I" and then it
hangs. Wouldn't that be out of memory or something?

tia,

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

Joel said:
try changing this section of code

If SubTotalRow > Row1 Then
ActiveWindow.SelectedSheets.HPageBreaks.Add _
Before:=Cells(subtotalrows(i - 1) + 1, 1)
Row1 = subtotalrows(i - 1) + RowsPerPage
PageNumber = PageNumber + 1
End If

:

I get a runtime error 1004, application or object definition on the line with
the asterisks. While debugging this I noticed that it runs through the 5th
iteration of "I" then it stops. This macro does the page setup on each page
of long spreadsheets. There is also a routine that does subtotals before it
runs through the page break and formatting. So excel might be choking.

When it stopped in the watch window the variables are:
x= 12
RowsPerPage = 99
Row1 = 111
SubTotalRows(I - 1) evaluates to 96 [the line that stops]

I noticed one strange thing. If I show the page break preview mode I get
the above error. If I run in regular mode then it stops in the same place
but I get a runtime error 9 instead and I don't get any values.

thanks for any help,
---------------code segment-----------


x = ActiveSheet.HPageBreaks.Count
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)
Debug.Print I
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
Click to expand...
Click to expand...
Click to expand...
 
J

Janis

JOel
I reposted this on a new thread because I marked this one as answered and I
thought it would get overlooked. In any case, the question may be the same
as on the new thread. The for loop with the x was commented out. My mistake
sorry for the confusion. I noticed it and I tested it without. I'll try
your suggestion here and if it works I'll close the new thread.
Sorry,
Janis

Joel said:
I only get your error if there are no page breaks on the page. If
pagebreakcount is less than 2 you will get an error because item 1 and/or 2
will not be valid.

Janis said:
I tried running it for over an hour. First the old way and then with your
code. It is stopping in the same place with both and I get the runtime
subscript out of range error on this line:
RowsPerPage = .Item(2).Location.Row - .Item(1).Location.Row

Any other ideas? TIA,
Janis
----code----
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


'RowsPerPage = ActiveSheet.HPageBreaks(2).Location.Row -
ActiveSheet.HPageBreaks(1).Location.Row
With ActiveSheet.HPageBreaks 'added by Tomlinson
RowsPerPage = .Item(2).Location.Row - .Item(1).Location.Row
End With
K = 1
PageNumber = 1
Row1 = 0

For I = 0 To UBound(SubTotalRows)
Debug.Print I
SubTotalRow = SubTotalRows(I)
If Row1 = 0 Then
Row1 = ActiveSheet.HPageBreaks(PageNumber).Location.Row
End If

' If SubTotalRow > Row1 Then
' ActiveWindow.SelectedSheets.HPageBreaks.Add
Before:=Cells(SubTotalRows(I - 1) + 1, 1)
' Row1 = SubTotalRows(I - 1) + RowsPerPage
' PageNumber = PageNumber + 1
' End If

If SubTotalRow > Row1 Then 'added by Joel
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

Joel said:
try changing this section of code

If SubTotalRow > Row1 Then
ActiveWindow.SelectedSheets.HPageBreaks.Add _
Before:=Cells(subtotalrows(i - 1) + 1, 1)
Row1 = subtotalrows(i - 1) + RowsPerPage
PageNumber = PageNumber + 1
End If

:

I get a runtime error 1004, application or object definition on the line with
the asterisks. While debugging this I noticed that it runs through the 5th
iteration of "I" then it stops. This macro does the page setup on each page
of long spreadsheets. There is also a routine that does subtotals before it
runs through the page break and formatting. So excel might be choking.

When it stopped in the watch window the variables are:
x= 12
RowsPerPage = 99
Row1 = 111
SubTotalRows(I - 1) evaluates to 96 [the line that stops]

I noticed one strange thing. If I show the page break preview mode I get
the above error. If I run in regular mode then it stops in the same place
but I get a runtime error 9 instead and I don't get any values.

thanks for any help,
---------------code segment-----------


x = ActiveSheet.HPageBreaks.Count
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)
Debug.Print I
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
Click to expand...
Click to expand...
Click to expand...
 
J

Janis

I don't know if there are page breaks on the page or not. If I put them on
manually it doesn't seem to matter. I reposted this thread with 2 fuctions.
The part that was commented out got changed to a function. Another function
was added because the page setup is getting to large to function but the new
function doesn't work. I think the pagebreaks may be in there.
Joel said:
I only get your error if there are no page breaks on the page. If
pagebreakcount is less than 2 you will get an error because item 1 and/or 2
will not be valid.

Janis said:
I tried running it for over an hour. First the old way and then with your
code. It is stopping in the same place with both and I get the runtime
subscript out of range error on this line:
RowsPerPage = .Item(2).Location.Row - .Item(1).Location.Row

Any other ideas? TIA,
Janis
----code----
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


'RowsPerPage = ActiveSheet.HPageBreaks(2).Location.Row -
ActiveSheet.HPageBreaks(1).Location.Row
With ActiveSheet.HPageBreaks 'added by Tomlinson
RowsPerPage = .Item(2).Location.Row - .Item(1).Location.Row
End With
K = 1
PageNumber = 1
Row1 = 0

For I = 0 To UBound(SubTotalRows)
Debug.Print I
SubTotalRow = SubTotalRows(I)
If Row1 = 0 Then
Row1 = ActiveSheet.HPageBreaks(PageNumber).Location.Row
End If

' If SubTotalRow > Row1 Then
' ActiveWindow.SelectedSheets.HPageBreaks.Add
Before:=Cells(SubTotalRows(I - 1) + 1, 1)
' Row1 = SubTotalRows(I - 1) + RowsPerPage
' PageNumber = PageNumber + 1
' End If

If SubTotalRow > Row1 Then 'added by Joel
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

Joel said:
try changing this section of code

If SubTotalRow > Row1 Then
ActiveWindow.SelectedSheets.HPageBreaks.Add _
Before:=Cells(subtotalrows(i - 1) + 1, 1)
Row1 = subtotalrows(i - 1) + RowsPerPage
PageNumber = PageNumber + 1
End If

:

I get a runtime error 1004, application or object definition on the line with
the asterisks. While debugging this I noticed that it runs through the 5th
iteration of "I" then it stops. This macro does the page setup on each page
of long spreadsheets. There is also a routine that does subtotals before it
runs through the page break and formatting. So excel might be choking.

When it stopped in the watch window the variables are:
x= 12
RowsPerPage = 99
Row1 = 111
SubTotalRows(I - 1) evaluates to 96 [the line that stops]

I noticed one strange thing. If I show the page break preview mode I get
the above error. If I run in regular mode then it stops in the same place
but I get a runtime error 9 instead and I don't get any values.

thanks for any help,
---------------code segment-----------


x = ActiveSheet.HPageBreaks.Count
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)
Debug.Print I
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
Click to expand...
Click to expand...
Click to expand...
 
J

Janis

actually you might be right. There might be another function that turns on
the pagebreaks. some other procedures work together with this one but this
one is the only one that doesn't work. perhaps I need to run it with the
other ones but if I do that then it dies?

Joel said:
I only get your error if there are no page breaks on the page. If
pagebreakcount is less than 2 you will get an error because item 1 and/or 2
will not be valid.

Janis said:
I tried running it for over an hour. First the old way and then with your
code. It is stopping in the same place with both and I get the runtime
subscript out of range error on this line:
RowsPerPage = .Item(2).Location.Row - .Item(1).Location.Row

Any other ideas? TIA,
Janis
----code----
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


'RowsPerPage = ActiveSheet.HPageBreaks(2).Location.Row -
ActiveSheet.HPageBreaks(1).Location.Row
With ActiveSheet.HPageBreaks 'added by Tomlinson
RowsPerPage = .Item(2).Location.Row - .Item(1).Location.Row
End With
K = 1
PageNumber = 1
Row1 = 0

For I = 0 To UBound(SubTotalRows)
Debug.Print I
SubTotalRow = SubTotalRows(I)
If Row1 = 0 Then
Row1 = ActiveSheet.HPageBreaks(PageNumber).Location.Row
End If

' If SubTotalRow > Row1 Then
' ActiveWindow.SelectedSheets.HPageBreaks.Add
Before:=Cells(SubTotalRows(I - 1) + 1, 1)
' Row1 = SubTotalRows(I - 1) + RowsPerPage
' PageNumber = PageNumber + 1
' End If

If SubTotalRow > Row1 Then 'added by Joel
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

Joel said:
try changing this section of code

If SubTotalRow > Row1 Then
ActiveWindow.SelectedSheets.HPageBreaks.Add _
Before:=Cells(subtotalrows(i - 1) + 1, 1)
Row1 = subtotalrows(i - 1) + RowsPerPage
PageNumber = PageNumber + 1
End If

:

I get a runtime error 1004, application or object definition on the line with
the asterisks. While debugging this I noticed that it runs through the 5th
iteration of "I" then it stops. This macro does the page setup on each page
of long spreadsheets. There is also a routine that does subtotals before it
runs through the page break and formatting. So excel might be choking.

When it stopped in the watch window the variables are:
x= 12
RowsPerPage = 99
Row1 = 111
SubTotalRows(I - 1) evaluates to 96 [the line that stops]

I noticed one strange thing. If I show the page break preview mode I get
the above error. If I run in regular mode then it stops in the same place
but I get a runtime error 9 instead and I don't get any values.

thanks for any help,
---------------code segment-----------


x = ActiveSheet.HPageBreaks.Count
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)
Debug.Print I
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
Click to expand...
Click to expand...
Click to expand...
 
J

Janis

JOel:
I ran all the related procedures and it ran until the same out of bounds
error I mentioned above. I can't tell you exactly where the pagebreaks are
counted but I will look for it on the weekend. It is possible you are right.
It was counted before but we were getting overflows so the two functions
were added. I will add them here and close the other thread. thanks for
your ideas.
janis

Joel said:
I only get your error if there are no page breaks on the page. If
pagebreakcount is less than 2 you will get an error because item 1 and/or 2
will not be valid.

Janis said:
I tried running it for over an hour. First the old way and then with your
code. It is stopping in the same place with both and I get the runtime
subscript out of range error on this line:
RowsPerPage = .Item(2).Location.Row - .Item(1).Location.Row

Any other ideas? TIA,
Janis
----code----
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


'RowsPerPage = ActiveSheet.HPageBreaks(2).Location.Row -
ActiveSheet.HPageBreaks(1).Location.Row
With ActiveSheet.HPageBreaks 'added by Tomlinson
RowsPerPage = .Item(2).Location.Row - .Item(1).Location.Row
End With
K = 1
PageNumber = 1
Row1 = 0

For I = 0 To UBound(SubTotalRows)
Debug.Print I
SubTotalRow = SubTotalRows(I)
If Row1 = 0 Then
Row1 = ActiveSheet.HPageBreaks(PageNumber).Location.Row
End If

' If SubTotalRow > Row1 Then
' ActiveWindow.SelectedSheets.HPageBreaks.Add
Before:=Cells(SubTotalRows(I - 1) + 1, 1)
' Row1 = SubTotalRows(I - 1) + RowsPerPage
' PageNumber = PageNumber + 1
' End If

If SubTotalRow > Row1 Then 'added by Joel
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

Joel said:
try changing this section of code

If SubTotalRow > Row1 Then
ActiveWindow.SelectedSheets.HPageBreaks.Add _
Before:=Cells(subtotalrows(i - 1) + 1, 1)
Row1 = subtotalrows(i - 1) + RowsPerPage
PageNumber = PageNumber + 1
End If

:

I get a runtime error 1004, application or object definition on the line with
the asterisks. While debugging this I noticed that it runs through the 5th
iteration of "I" then it stops. This macro does the page setup on each page
of long spreadsheets. There is also a routine that does subtotals before it
runs through the page break and formatting. So excel might be choking.

When it stopped in the watch window the variables are:
x= 12
RowsPerPage = 99
Row1 = 111
SubTotalRows(I - 1) evaluates to 96 [the line that stops]

I noticed one strange thing. If I show the page break preview mode I get
the above error. If I run in regular mode then it stops in the same place
but I get a runtime error 9 instead and I don't get any values.

thanks for any help,
---------------code segment-----------


x = ActiveSheet.HPageBreaks.Count
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)
Debug.Print I
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
Click to expand...
Click to expand...
Click to expand...
 
J

Janis

It is possible you are right. There was a big page break overflow problem so
a separate function was added to take the load off creating the subtotals.
The pagebreak count could be missing. I'll look for it. There are several
other routines that are run before this one. I ran them all and it still
stopped on the out of bounds subscript.

here is the code with the new get subtotal function maybe it counts the
pagebreaks there. i'll close the other thread.

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()




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


'RowsPerPage = ActiveSheet.HPageBreaks(2).Location.Row -
ActiveSheet.HPageBreaks(1).Location.Row
With ActiveSheet.HPageBreaks 'added by Tomlinson
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
ActiveWindow.SelectedSheets.HPageBreaks.Add
Before:=Cells(SubTotalRows(I - 1) + 1, 1)
Row1 = SubTotalRows(I - 1) + RowsPerPage
PageNumber = PageNumber + 1
End If

' If SubTotalRow > Row1 Then 'added by Joel
' Set ActiveSheet.HPageBreaks(PageNumber).Location =
Cells(SubTotalRows(I - 1) + 1, 1)
' Row1 = SubTotalRows(I - 1) + RowsPerPage
' PageNumber = PageNumber + 1
' End If
Next I



Application.ScreenUpdating = True
ActiveWindow.View = xlNormalView
Range(FirstDataCell).Activate
Range("A1").Activate

End Sub



'
'
'
''
Private Function GetSubTotalRows()

Dim UsedRange1 As Range
Dim Rows() As Variant
Dim I As Long
Dim UsedCol1 As Long
Dim C As Range




Set UsedRange1 = Intersect(Range(ServiceGroupColumn & FirstDataRow & ":" &
ServiceGroupColumn & ActiveSheet.UsedRange.Rows.Count), ActiveSheet.UsedRange)
Set UsedRange1 = UsedRange1.SpecialCells(xlCellTypeBlanks)

UsedCol1 = UsedRange1.Columns.Count

I = 0
For Each C In UsedRange1
If IsSubTotalRow(C.Row, UsedCol1) = True Then
ReDim Preserve Rows(I)
Rows(I) = C.Row
I = I + 1
End If

Next C

GetSubTotalRows = Rows()


End Function


Private Function IsSubTotalRow(ByVal H As Integer, ByVal x As Integer) As
Boolean
Dim C As Range
Dim Value2 As Variant

IsSubTotalRow = True
For Each C In Range(Cells(H, 1), Cells(H, x))
'C.Select
Value2 = CStr(C.Value2)
If Left(C.Formula, 6) <> "=SUMIF" Then
If CStr(C.Value2) <> "" Then
IsSubTotalRow = False
End If
End If
Next C


End Function
'

Joel said:
I only get your error if there are no page breaks on the page. If
pagebreakcount is less than 2 you will get an error because item 1 and/or 2
will not be valid.

Janis said:
I tried running it for over an hour. First the old way and then with your
code. It is stopping in the same place with both and I get the runtime
subscript out of range error on this line:
RowsPerPage = .Item(2).Location.Row - .Item(1).Location.Row

Any other ideas? TIA,
Janis
----code----
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


'RowsPerPage = ActiveSheet.HPageBreaks(2).Location.Row -
ActiveSheet.HPageBreaks(1).Location.Row
With ActiveSheet.HPageBreaks 'added by Tomlinson
RowsPerPage = .Item(2).Location.Row - .Item(1).Location.Row
End With
K = 1
PageNumber = 1
Row1 = 0

For I = 0 To UBound(SubTotalRows)
Debug.Print I
SubTotalRow = SubTotalRows(I)
If Row1 = 0 Then
Row1 = ActiveSheet.HPageBreaks(PageNumber).Location.Row
End If

' If SubTotalRow > Row1 Then
' ActiveWindow.SelectedSheets.HPageBreaks.Add
Before:=Cells(SubTotalRows(I - 1) + 1, 1)
' Row1 = SubTotalRows(I - 1) + RowsPerPage
' PageNumber = PageNumber + 1
' End If

If SubTotalRow > Row1 Then 'added by Joel
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

Joel said:
try changing this section of code

If SubTotalRow > Row1 Then
ActiveWindow.SelectedSheets.HPageBreaks.Add _
Before:=Cells(subtotalrows(i - 1) + 1, 1)
Row1 = subtotalrows(i - 1) + RowsPerPage
PageNumber = PageNumber + 1
End If

:

I get a runtime error 1004, application or object definition on the line with
the asterisks. While debugging this I noticed that it runs through the 5th
iteration of "I" then it stops. This macro does the page setup on each page
of long spreadsheets. There is also a routine that does subtotals before it
runs through the page break and formatting. So excel might be choking.

When it stopped in the watch window the variables are:
x= 12
RowsPerPage = 99
Row1 = 111
SubTotalRows(I - 1) evaluates to 96 [the line that stops]

I noticed one strange thing. If I show the page break preview mode I get
the above error. If I run in regular mode then it stops in the same place
but I get a runtime error 9 instead and I don't get any values.

thanks for any help,
---------------code segment-----------


x = ActiveSheet.HPageBreaks.Count
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)
Debug.Print I
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

runtime error #9 subscript out of bounds #4 2
run time error #9, subscript out of range 4
runtime error 9 subscript out of range #3 3
runtime error 9 out of range subscript 1
runtime error 9 out of range subscript #2 6
runtime error 9, subscript out of bounds error #2 1
Excel 2013 Merge Data 2
cannot insert row in vba 1

Top