Excel page breaks and print settings

[l7eslie3]

I'm flying blind here, no previous VB programming experience before I needed to get this project done!

I am starting with an dump of data exported to Excel which includes rows ordered by a location ID and a group ID (several groups for each location). What I need is to print the data out for each group, with the groups separated on individual pages (groups may overflow to more than one page also). Ex: group 1 has 20 members, fitting on page 1. Group 2 has 50 members, which takes up a page and a half (pages 2 and 3). Group 3 should be printed starting on page 4.

To achieve this I have created a macro which uses a variable to store the group ID and then goes line by line to check if the next row is in the same group. It copies all members of one group to a separate sheet, adjusts the print margins to include all the data, and prints the group. Then it clears the sheet and proceeds with the next group.

My question is as follows. Is there any way to get this printed as ONE print job? Other than making a new sheet for each group? To simplify printing I coded the print setup into the macro, but it sends each group as its own print job. I need users at other computers to be able to print this also, and printer installation settings vary. If I could bring up the print dialog ONCE, they could choose their printer of choice and also put in their copy code. We use print job accounting and our IT guy is wary of disabling it for this one purpose.

Hope this is not too confusing! If anyone needs clarification or code excerpts, please let me know.

THANKS!

[Edit: I suppose an easy way to do it would be to keep all the data on one sheet and set the page breaks after each group. Is there any VB command to do this in Excel?]

[l7eslie3]

Bump for my edit..thankee

[westconn1]

insert a page break after each group

oops, just read your edit
try to recod a macro to insert page break to get some sample code to work with

there is a way to create a sheet that has a link to several other sheets for printing mutiple sheets at once, but it is a few years since i did that