elsuji
09-07-2019, 09:47 AM
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
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