Print pagebreak code

  pickle factory 11:10 30 Jun 06
Locked

Hello folks, don't suppose anyone could gimme a hand with what I'm missing in this bit of code? I have a sheet which is to be printed in two variations (Bloody TS standard requirements). I intend to have three macro's, one macro that will hide certain rows/columns, set page breaks and print settings to print correctly for that variant, another macro to do likewise but for the second variant and a third I already have, which simply removes all the hides and displays the entire sheet. My problem is that the code only runs as far as third page break before I get 'Run time error 9' 'Subscript out of range'. I've tried to set the overall print area which is the correct range (B10:N791). It sets the first two breaks OK, I don't understand what is stopping it from continuing? Any ideas? I'm probably missing something obvious.


Sub ManLayout()
'
' Manufacturing Layout Print Set-Up
'
Rows("2:9").Select
Selection.EntireRow.Hidden = True
Columns("O:V").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.PageSetup.PrintArea = "$B$10:$N$791"
Set ActiveSheet.HPageBreaks(1).Location = Range("B104")
Set ActiveSheet.HPageBreaks(2).Location = Range("B208")
Set ActiveSheet.HPageBreaks(3).Location = Range("B304")
Set ActiveSheet.HPageBreaks(4).Location = Range("B400")
Set ActiveSheet.HPageBreaks(5).Location = Range("B504")
Set ActiveSheet.HPageBreaks(6).Location = Range("B608")

End Sub

Thanks

  VoG II 11:22 30 Jun 06

The code runs fine for me so I don't understand why it is crashing. It will run a little faster if you avoid Select statements:

Sub ManLayout()
'
' Manufacturing Layout Print Set-Up
'
Rows("2:9").EntireRow.Hidden = True
Columns("O:V").EntireColumn.Hidden = True
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.PageSetup.PrintArea = "$B$10:$N$791"
Set ActiveSheet.HPageBreaks(1).Location = Range("B104")
Set ActiveSheet.HPageBreaks(2).Location = Range("B208")
Set ActiveSheet.HPageBreaks(3).Location = Range("B304")
Set ActiveSheet.HPageBreaks(4).Location = Range("B400")
Set ActiveSheet.HPageBreaks(5).Location = Range("B504")
Set ActiveSheet.HPageBreaks(6).Location = Range("B608")
End Sub

  pickle factory 11:27 30 Jun 06

Just found my problem, for some reason I only had 3 pages visible on page break view. I reset all page breaks manually, ran code and it worked fine. I just need to find how to reset page breaks in my code prior to running the rest. I'll add your tweaks, thanks.

This thread is now locked and can not be replied to.

What is Amazon Go and will it come to the UK? The store without checkouts or queues

1995-2015: How technology has changed the world in 20 years

Why ecommerce hasn't taken off on social media

New MacBook Pro 2016 review | MacBook Pro with Touch Bar review: Apple's expensive and powerful…