I Created Following Routine To Hide Rows In Groups Of 6, If Data Is Not Found In Column C. After Hiding All The Unneeded Rows, The Routine Would
Move The Automatic Page Breaks To Rows Indicated By The Dimensioned Variable, New_Page_Breaks, But I Keep Getting A Run-time Errer 438 On The Following Command: Set ActiveSheet.HPageBreak(J).Location = Range("A" & New_Page_Breaks(J)). Can Someone Help.
Sub Consoildate_Manifest()
'
Call Expand_Manifest
ActiveSheet.ResetAllPageBreaks
ActiveSheet.PageSetup.Zoom = 73
Page_Breaks = 1
Number_Of_Stores = 0
For I = 16 To Last_Manifest_Store_Row Step 6
If Cells(I + 2, 3) = "" Then
J = I
Do
J = J + 6
Loop Until Cells(J + 2, 3) <> "" Or J = Ending_Manifest_Row
Range(Cells(I, 2), Cells(J - 1, 5)).Select
Selection.EntireRow.Hidden = True
I = J
Number_Of_Stores = Number_Of_Stores + 1
If Number_Of_Stores = 10 Then
New_Page_Breaks(Page_Breaks) = J
Cells(Page_Breaks, 7) = J
Cells(1, 8) = Page_Breaks
Page_Breaks = Page_Breaks + 1
Number_Of_Stores = 1
End If
End If
Next I
ActiveWindow.View = xlPageBreakPreview
Application.ScreenUpdating = False
For J = 1 To Page_Breaks - 1
Set ActiveSheet.HPageBreak(J).Location = Range("A" & New_Page_Breaks(J))
Next J
Application.ScreenUpdating = True
ActiveWindow.View = xlNormalView
Range(Cells(1, 2), Cells(1, 2)).Select
'
End Sub
Move The Automatic Page Breaks To Rows Indicated By The Dimensioned Variable, New_Page_Breaks, But I Keep Getting A Run-time Errer 438 On The Following Command: Set ActiveSheet.HPageBreak(J).Location = Range("A" & New_Page_Breaks(J)). Can Someone Help.
Sub Consoildate_Manifest()
'
Call Expand_Manifest
ActiveSheet.ResetAllPageBreaks
ActiveSheet.PageSetup.Zoom = 73
Page_Breaks = 1
Number_Of_Stores = 0
For I = 16 To Last_Manifest_Store_Row Step 6
If Cells(I + 2, 3) = "" Then
J = I
Do
J = J + 6
Loop Until Cells(J + 2, 3) <> "" Or J = Ending_Manifest_Row
Range(Cells(I, 2), Cells(J - 1, 5)).Select
Selection.EntireRow.Hidden = True
I = J
Number_Of_Stores = Number_Of_Stores + 1
If Number_Of_Stores = 10 Then
New_Page_Breaks(Page_Breaks) = J
Cells(Page_Breaks, 7) = J
Cells(1, 8) = Page_Breaks
Page_Breaks = Page_Breaks + 1
Number_Of_Stores = 1
End If
End If
Next I
ActiveWindow.View = xlPageBreakPreview
Application.ScreenUpdating = False
For J = 1 To Page_Breaks - 1
Set ActiveSheet.HPageBreak(J).Location = Range("A" & New_Page_Breaks(J))
Next J
Application.ScreenUpdating = True
ActiveWindow.View = xlNormalView
Range(Cells(1, 2), Cells(1, 2)).Select
'
End Sub