forced page breaks

[sedgely]

Is it possible to 'force' a page break in Excel dependant on the value in a cell? for example if you had a list of data for printing where one of the columns was a date field how could you force excel to start a new page at each change in date?

Craig

 

[SkipVought]

Hi,

One simple way is to use the Subtotal Wizard and select Page Break between groups.

Or you could write a routine. Macro record inserting a page break as a model for that code (I do this ALL THE TIME!)

Loop thru your list and at the appropriate row, add the HPageBreaks.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[sedgely]

Hi Skip
Like the sound of the second option, but not sure how to do the loop, can you advise?

Craig

 

[SkipVought]

sPrev = ""
With ActiveSheet.UsedRange
  For r = .Row to .Row + .Rows.Count - 1
    sThis = Cells(r, DateColumn).Value
    If sThis <> sPrev AND sPrev <> &quot;&quot; Then _
           Activesheet.HPageBreaks.Add Before:=Cells(r, DateColumn)
    sPrev = sThis
  Next
End With

Note: DateColumn is the column number containg Date

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[sedgely]

Thanks Skip
That did the trick

Craig