Batch printing multiple Excel worksheets 1

[khschenk]

hello:

We have a macro that prints from multiple worksheets in an Excel workbook. The problem is that I am trying to create a PDF file from the worksheets. When this runs, Excel wants to create a separate PDf file for each worksheet. Any suggestions would be helpful.

Thanks.
--Kurt

Example of macro code:

Sub PrintHMOall()
Sheets("HMO&quot.Select
cTerritory = Cells(69, 9)
Sheets("HMO&quot.Select
Range("A1:J50&quot.Select
Selection.PrintOut Copies:=1
Sheets("HMO_R&quot.Select
Range("L1:T31&quot.Select
Selection.PrintOut Copies:=1
Range("A1:K44&quot.Select
Selection.PrintOut Copies:=1
Range("A45:K89&quot.Select
Selection.PrintOut Copies:=1
If cTerritory = "E" Then
Range("A90:K133&quot.Select
Selection.PrintOut Copies:=1
Range("A134:K178&quot.Select
Selection.PrintOut Copies:=1
ElseIf cTerritory = "F" Then
Range("A90:K133&quot.Select
Selection.PrintOut Copies:=1
Range("A134:K178&quot.Select
Selection.PrintOut Copies:=1
End If
Sheets("HMO&quot.Select
Range("A1&quot.Select
Range("C2&quot.Select
End Sub

 

[DaleWatson123321]

khschenk,

Here's a probable solution...

First read my FAQ - faq68-1161 on Printing Disjointed Ranges on ONE Page.

Also, be aware that another option, is to use the "Camera". You can install it as a toolbar icon by using these steps:

1) Right-click on the existing toolbar area, and choose "Customize".

2) Click on the "Commands" tab.

3) Under "Categories" (left side), click "Tools".

4) Under "Commands" (right side), click the scroll-bar and scroll to the bottom where you'll find "Camera" (3rd from bottom).

5) Click-and-drag "Camera" to an existing toolbar.

6) To create a &quot;hotkey&quot;, right-click on the &quot;Camera&quot;, and choose &quot;Text Only (Always)&quot;. Because the name has the &quot;&&quot; character in front of it, the &quot;C&quot; becomes underlined, meaning you can use the &quot;C&quot; as a &quot;hotkey&quot; when you hold down the <Alt> key.

So, &quot;taking a picture&quot; is as easy as: <Alt> C

HOWEVER... BEFORE taking each picture, I would STRONGLY recommend you assign a &quot;Range Name&quot;. You've already begun to name your sheets. Naming &quot;ranges&quot; is even MORE important.

By naming ranges, you can reference these ranges WITHOUT having to reference the sheet name.

So, the basic process should be:

A) Create a range name:
1) Highlight the range
2) Hold down the <Control> key and hit <F3>
3) Type a name
4) <Enter>

Caution: Don't create names that will conflict with cell addresses or vba commands. Use a &quot;_&quot; character when in doubt - e.g. use &quot;Range_1&quot; rather than &quot;range&quot;.

B) Take a picture:

1) While the range is still highlighted, use <Alt> C (if you've created the &quot;hotkey&quot. If not, click the Camera.

2) Click on the separate page from where you'll print the PDF.

3) Click on the sheet, and your &quot;picture&quot; will be pasted.

4) While still clicked on the picture, immediately type =range1 <Enter> ...where range1 is the name you've used to assign to that range. You'll then see that this name is entered into the &quot;Formula Bar&quot; as a formula.

5) Drag the picture to the desired position. If desired, you can also enlarge and/or stretch - just by clicking one of the &quot;handles&quot; and dragging.

Repeat steps A & B for each of the ranges you want to include on your separate sheet.

For your &quot;IF&quot; condition, you could still have a routine where you would have one range or another range selected, based on your condition.

Or, depending on the specifics, you might be better off to have TWO SEPARATE SHEETS, and then have your routine pick one or the other sheet, depending on your condition.

I hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

Hi khschenk,

...Just checking the status of my postings.

Any solution yet ?

Did you understand the suggestion I made ?

If you've come up with a solution, the rest of us at Tek-Tips would REALLY appreciate you're sharing it with us. Thanks.

If you need more help, please advise.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca

 

[beniez]

Dale

Really interesting - I've never come across the Camera before, in years of using XL. Now all I have to do is think of a use for it.....

Regards

Ben

 

[khschenk]

Unfortuately I have been crushed with other work have not been able to apply this solution. I will certainly let you know how I make out. I appreciate your suggestions.

Thanks.
--Kurt

 

[DaleWatson123321]

You're indeed welcome, Kurt.

...thanks for the feedback, and the STAR.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca