PDA

View Full Version : Page break setup



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

Max_iR
09-07-2019, 02:50 PM
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
09-07-2019, 10:09 PM
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
09-08-2019, 02:10 PM
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.