CODEWITHSUNDEEP
excelvba
Tom C.
Tom C.

Reputation: 1

Printing areas on several sheets through

I would like to know if I can "manually" (through VBA) reorganize the order in which printing areas are printed.

Let's say I have an Excel document with 4 sheets, Sheet1 to Sheet4. In Sheet2 and Sheet3, depending on situations, I may have 1 or several printing areas (i.e. several pages to print), 1 printing area = 1 full page. Other sheets are "regular" (single printing area, single sheet of paper printed)

IF I have only one page in each case, it's piece of cake... I print SheetX to page X.

BUT users can click a button to create one (or several) new page (s) in Sheet2 and/or Sheet3. I do NOT want to let them create new sheets as they will mess up all. Depending on previous choices, some sheets are displayed or hidden; in this case of several pages in the same sheet, printing areas are defined in VBA today.

IF I have several pages/areas defined in sheet2 and Sheet3, I would like to print them in this order: Sheet1, Sheet2-area1, Sheet3-area1, Sheet2-area2, Sheet3-area2, Sheet4.

In any case, the printing of the document goes through a VBA macro and a user defined button.

My other solution would be to change page numbers with VBA and print all sheets in order, then users will have to reorder pages manually. Not a big deal with 10-20 pages documents, but still a source of mistakes and complaints.

I think I am more in need of a general idea of how to do it than code snippets. I cannot see where this can be done. I know how to use

ActiveWorkbook.SheetX.PageSetup.printArea = "B2:J34","L2:,S34"

I would need something like

ActiveWorkbook.PageSetup.printArea = Sheet1!"A1:H20", Sheet2!"B2:J34", Sheet3!"B2:L50", Sheet2!"L2:S34", Sheet3!"N2:V50", Sheet4!"A2:K22"

Any clue?

Upvotes: 0

Views: 35

Answers (1)

Mark Grecco
Mark Grecco

Reputation: 63

Just an idea but what if you use a macro to assemble all the print areas into a new workbook as individual sheets. Put the sheets in the order you want them printed and then print that workbook and close it. It's probably a little more overhead with all the range copying but could give you more control.

Upvotes: 0

Related Questions