Page Break code Not working

abeed

Board Regular
Joined
Nov 19, 2005
Messages
79
Hi:I am trying to finalize a report. In a single excel sheet, I have 6 data sets from 6 tables. I want to put only 2 table data per page. Say after 2 data set, I will give a page break at row no3 after the last data cell in that data area. Each data set has a word "ami" in header line(That I can search). How ca I give a page break. the code has some errors.
--------
sub mysub()
ActiveSheet.ResetAllPageBreaks
Columns("A:A").Select
rend = Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To rend
Set fndrng = Selection.Find(what:="ami", After:=ActiveCell)
If Not fndrng Is Nothing Then
j = j + 1
Cells(fndrng.Row, 1).Activate
r = ActiveCell.End(xlDown).Row
i = r
If (j = 2) Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(r + 3, 1)
j = 0
End If
Else
Exit Sub
End If
Next i
End Sub

--Where is the error and howw can I simplify the code? I know its not efficient.
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

gaynard_nelson

Active Member
Joined
Dec 4, 2002
Messages
323
abeed said:
Hi:I am trying to finalize a report. In a single excel sheet, I have 6 data sets from 6 tables. I want to put only 2 table data per page. Say after 2 data set, I will give a page break at row no3 after the last data cell in that data area. Each data set has a word "ami" in header line(That I can search). How ca I give a page break. the code has some errors.
--------

Have you checked Page Setup to see if you have Page, Scaling, Fit to selected rather than Scaling, Adjust to, if so excel ignores any Page Break settings.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,436
Messages
5,572,109
Members
412,441
Latest member
kelethymos
Top