To set 4 PageBreaks by VBA in Worksheet [Closed]

Report
-
 issa -
Dears,

I would be very grateful If someone can help me.

I have several sheets in one excel file. I need one command button when pressed to set 4 Pagebreaks in each worksheet.

The Ranges for 4 pageBreaks are:

PageBreak 1: range = A1:W51
PageBreak 2: range = A52:W89
PageBreak 3: range = A90:W132
PageBreak 4: range = A133:W150

And these 4 Pagebreaks have to be the same for each worksheet in the workbook.

Thanks a lot in advance for any kind assistant.

1 reply

Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
118
Use the following code:

Sub Macro1()
    Dim sht As Worksheet
    For Each sht In Worksheets
    ActiveWindow.View = xlPageBreakPreview
    Set ActiveSheet.VPageBreaks(1).Location = Range("X1")
        
    Set ActiveSheet.HPageBreaks(1).Location = Range("A52")
    
    Set ActiveSheet.HPageBreaks(2).Location = Range("A90")
        
    Set ActiveSheet.HPageBreaks(3).Location = Range("a133")
        
    Set ActiveSheet.HPageBreaks(4).Location = Range("a150")
    ActiveWindow.View = xlNormalView
    
    Next sht
    
End Sub
First of all thanks a lot to your help.

It didn't succeed 100%, I faced a message (Run-Time error '1004')

But I discovered that I need first to unhide any hidden row then to apply this macro but I make some adjustments as follow:

Private Sub CommandButton3_Click()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Select
ActiveWindow.View = xlNormalView
ActiveWindow.View = xlPageBreakPreview
ws.ResetAllPageBreaks
Set ActiveSheet.HPageBreaks(1).Location = Range("A52")
Set ActiveSheet.HPageBreaks(2).Location = Range("A90")
Set ActiveSheet.HPageBreaks(3).Location = Range("a133")

Next ws
End Sub

But I need the Vertical as well, so if I added this to the above code I face the same message (Run-Time error '1004').
Set ActiveSheet.VPageBreaks(1).Location = Range("X1")

So what can I add to unhide first any hidden rows then to apply the macro?

Thank again for any help.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!