Previous Topic Next Topic

To set 4 PageBreaks by VBA in Worksheet

Closed
issa - May 10, 2011 at 07:13 AM
 issa - May 11, 2011 at 07:21 AM
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.
Related:

1 response

Answer 1 / 1
RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 120
May 11, 2011 at 03:37 AM
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
0
issa
May 11, 2011 at 07:21 AM
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.
0