Concatenate printareas

PaulS

Board Regular
Joined
Feb 26, 2006
Messages
66
To the Excel/Printing expert,
I made a macro for printing the 12 sheets (named 'jan', 'feb'...etc).
Besides some user questions the macro has 12 times the following statement:
(generated via the 'recording macro' function)
Sheets("jan").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
For each sheet the printing area has been defined for the maximum number of lines.
Printing of 12 reports works nice, but
I have two questions:
1.The report is printed on the default printer. This is not what I want. I would like the 'ín between window' where I can select the printer. How do I get that window?
2.Early in the year the monthly reports are short. Is it possible to set the printarea in the macro? The lines to be printed can easily be calculated: (data lines start at row 7) col A holds a number . The highest number is the last line to be printed. Can printed areas of the 12 sheets be printed in one report (header taken from sheet 'jan' – rows 1-6)?
Thanks for your help.
Paul
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Application.Dialogs(xlDialogPrint).Show
 

PaulS

Board Regular
Joined
Feb 26, 2006
Messages
66
Thank you. Q1 solved. That's what I was looking for. Where can I find such VBA coding instructions?
Any idea about my second question? Is it possible?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
You can set the print area like this

Code:
ActiveSheet.PageSetup.PrintArea = "A7:N" & Range("A" & Rows.Count).End(xlUp).Row

Change N to your rightmost column.

I don't know a way (other than by copying and pasting to a separate sheet) of concatenating the printing from several sheets.
 

PaulS

Board Regular
Joined
Feb 26, 2006
Messages
66
Thank you Peter,
I will try to implement your suggestion of copying the rows to a new worksheet, which is for printing only.
Thanks,
Paul
 

Forum statistics

Threads
1,144,526
Messages
5,724,849
Members
422,585
Latest member
k3n

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top