Moving Automatic Page Breaks

Andre1075

New Member
Joined
Jan 13, 2009
Messages
3
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,979
Messages
5,767,432
Members
425,412
Latest member
andrealp4444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top