auto set page break to last row with data

[wilg]

I have a sheet with data from columns a to v and row 2 to variable end.

If on a specific sheet ("3 Summary") I would like to automatically set the horizontal bage break to the last row which could be row 200 to 300.

If been trying to use many codes I've come across but it does not seem to work. Any help is appreciated.

[mdmackillop]

I don't know if you want the break before or after the last cell: this is for before
[VBA]Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim c As Range
If Sh.Name = "3 Summary" Then
Set c = Range("A:V").Find("*", searchorder:=xlRows, searchdirection:=xlPrevious)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=c 'or Before:=c.Offset(1)
End If
End Sub
[/VBA]

[wilg]

Hi mdmackillop..

Sorry for delayed reply, but this does not seem to work for me. I get no movement of the pagebreaks what so ever.

[mdmackillop]

Can you post a sample workbook and indicate where the page breaks should be?

[wilg]

It would be difficult to post the workbook. I tested your code on a blank workbook and it does work.

With my workbook I have formulas in column a and column b. column a if iserror give value ""

Would that be a factor?

[mdmackillop]

I cannot see that it would.