automatic page break

R

Randy

Why won't the following macro work to create a page break - in particular it
is giving me an error at the InStr line

Sub b8Pagebrk()
Dim theRange As Range
Dim lastrow&, firstRow&, x&
Set theRange = ActiveSheet.UsedRange
lastrow = theRange.Cells(theRange.Cells.Count).Row
firstRow = theRange.Cells(1).Row
For x = lastrow To firstRow Step -1
If InStr(1, Cells(x, 1), "Total") > 0 Then
Cells(x + 1, 1).PageBreak = xlManual

End If
Next
End Sub
 
D

Dave Peterson

The only time I've seen this kind of line:
If InStr(1, Cells(x, 1), "Total") > 0 Then
cause an error is when there's an error in that cell.

I'd try:
If InStr(1, Cells(x, 1).Text, "Total") > 0 Then

I've seen it when x isn't a valid row number, but that doesn't look like the
problem here.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

R1C1 1
R1C1 0
end it 2
HELP Copy And Paste 7
Paste Special throwing an error 0
Need Excel Macro to Find Specific Text and Characters 1
Creating a fixture list 3
Code to delete rows based on value 11

Top