Printing subtotals on same page
 

I have a large spreadsheet that I have setup in groups of 40 rows of 60. The
60
categories are separated by a row that is formatted with a yellow
background. Is there a way to create a print macro that would recognize the
yellow and keep everything beneath the yellow on the same page. Something
like in Access where you can create a report and use the Keep Together rule
so that if the list gets to the bottom of the page and there is not enough
room on the page to print the category with the rows of that section, it
would start a new page. The sixty rows beneath the categories can fluctuate
depending on if there is data in the rows so sometimes there could be 1 row
and sometimes 10 and sometimes 60. Any suggestions?

Printing subtotals on same page
 

Rob,

You can automate adding horizontal page breaks (as you would do manually in
Page Break Preview);

Dim lRowCounter As Long

Do Until IsEmpty(ActiveSheet.Cells(lRowCounter, 1))

If ActiveSheet.Cells(lRowCounter, 1).Interior.Color = vbYellow Then

ActiveSheet.HPageBreaks.Add
Befo=ActiveSheet.Cells(lRowCounter, 1)

End If

lRowCounter = lRowCounter + 1

Loop

This relies on there always being data in column 1 & I can't get a handle on
how to clear them off first (so difficult to repeat). The Delete method seems
to be giving me some grief, I will post back if I get any joy.

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Rob" wrote:

I have a large spreadsheet that I have setup in groups of 40 rows of 60. The
60
categories are separated by a row that is formatted with a yellow
background. Is there a way to create a print macro that would recognize the
yellow and keep everything beneath the yellow on the same page. Something
like in Access where you can create a report and use the Keep Together rule
so that if the list gets to the bottom of the page and there is not enough
room on the page to print the category with the rows of that section, it
would start a new page. The sixty rows beneath the categories can fluctuate
depending on if there is data in the rows so sometimes there could be 1 row
and sometimes 10 and sometimes 60. Any suggestions?