Page Break code Not working

[abeed]

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.

 

[gaynard_nelson]

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.