Page break setup

[elsuji]

Hi

I am having the following code for page break on my excel sheet

Dim sh As Worksheet, Rg As Range, LastRow1 As Long, Count As Long ', ii As Longii = 45 ' first page break
Set sh = Sheets("Sheet7")
With sh
    LastRow1 = .Range("B" & .rows.Count).End(xlUp).Row
    Count = LastRow1
    Set Rg = .Range("B4", "G" & Count) 'The range of the document
    If LastRow1 > 5 Then ' count is the number of row. Break at every 15 rows
        .ResetAllPageBreaks
        .PageSetup.PrintArea = Rg.Address
        While Count > 0 And ii < LastRow1
            If Count > 10 Then ' no page break if there is less than 15 rows left
                '.Rows(ii).PageBreak = xlPageBreakManual
                .HPageBreaks.Add Before:=.rows(ii)
            End If
            ii = ii + 41
            Count = Count - 41
        Wend
    End If
    End With

I want to use the same code for all my work sheets ( Sheet4, Sheet5, Sheet6, Sheet7, ,Sheet8 )

Can any one help me how to do this

[Max_iR]

Hi

I am having the following code for page break on my excel sheet

Dim sh As Worksheet, Rg As Range, LastRow1 As Long, Count As Long ', ii As Longii = 45 ' first page break
Set sh = Sheets("Sheet7")
With sh
    LastRow1 = .Range("B" & .rows.Count).End(xlUp).Row
    Count = LastRow1
    Set Rg = .Range("B4", "G" & Count) 'The range of the document
    If LastRow1 > 5 Then ' count is the number of row. Break at every 15 rows
        .ResetAllPageBreaks
        .PageSetup.PrintArea = Rg.Address
        While Count > 0 And ii < LastRow1
            If Count > 10 Then ' no page break if there is less than 15 rows left
                '.Rows(ii).PageBreak = xlPageBreakManual
                .HPageBreaks.Add Before:=.rows(ii)
            End If
            ii = ii + 41
            Count = Count - 41
        Wend
    End If
    End With

I want to use the same code for all my work sheets ( Sheet4, Sheet5, Sheet6, Sheet7, ,Sheet8 )

Can any one help me how to do this

try :

Sub test()
Dim sh As Worksheet, Rg As Range, LastRow1 As Long, Count As Long ', ii As Longii = 45 ' first page break
For Each sh In Worksheets
With sh
    LastRow1 = .Range("B" & .Rows.Count).End(xlUp).Row
    Count = LastRow1
    Set Rg = .Range("B4", "G" & Count) 'The range of the document
    If LastRow1 > 5 Then ' count is the number of row. Break at every 15 rows
        .ResetAllPageBreaks
        .PageSetup.PrintArea = Rg.Address
        While Count > 0 And ii < LastRow1
            If Count > 10 Then ' no page break if there is less than 15 rows left
                '.Rows(ii).PageBreak = xlPageBreakManual
                .HPageBreaks.Add Before:=.Rows(ii)
            End If
            ii = ii + 41
            Count = Count - 41
        Wend
    End If
    End With
    Next
End Sub

[elsuji]

This is working great. But this should only work on Sheet4, Sheet5, Sheet6, Sheet7, ,Sheet8 . Because i am having Sheet1, Sheet2, Sheet3 with other formats. If I run this code this it is change the other sheet alignments

[jolivanes]

Re: But this should only work on Sheet4, Sheet5, Sheet6, Sheet7, ,Sheet8

See here if you only want it for certain sheets.
http://www.vbaexpress.com/forum/showthread.php?65803-Delete-Blank-Rows-and-Add-Borders-on-Last-Row

Maybe remember that this is a "help" forum, not a "do this for me" forum.