RE: H&F in Excel - Yes

Subject: RE: H&F in Excel - Yes
From: "Patricia L. Swart" <swart -at- RWT -dot- com>
To: "TECHWR-L" <techwr-l -at- lists -dot- raycomm -dot- com>
Date: Fri, 14 Sep 2001 17:56:11 -0400

Swati --

I don't know if you've received an answer on how to do this yet (I didn't
see one on the list), but
you can make a macro that accomplishes this.

Since I'm not a VB programmer, I recorded a macro and it works great.

Here's how to do it:

1) In your worksheet, set the print area (you can also name the print area
for easier ID in your code).

2) Go to Tools>Macro>Record New Macro.
The 'Record Macro' dialog box comes up. Type in a name for your Macro
(don't use spaces); click OK. This displays a floating 'recording' toolbar
on your worksheet.

3) In your worksheet, go to File>Page Setup>Header/Footer tab. Create your
header and footer for page 1. You could either choose a preset Header/Footer
or you can create your own using the Custom Header and Custom Footer
buttons. (For the exact process of how to create custom headers and
footers, see the post from Jenise Cook.)

4) Once you've created your header and footer (while still in the
Header/Footer tab), click the Print button. For the print range, choose the
Page(s) option and enter "1" in the 'From' box, and also enter "1" in the
'To' box; click OK. (You can choose other printer setups while in this
stage. This will print out page 1.)

5) Repeat Steps 3 and 4 above for as many pages as you have in your
worksheet (substituting the page number in Step 4 for the page number in
your worksheet).

6) Once you've completed Steps 3 and 4 above for all of your pages, press
the 'Stop Recording' button on the floating toolbar.
Now your macro is recorded.

7) You can assign this macro to a toolbar button by using the following
procedure edited from Microsoft Excel Help:

You can run a macro from a button on a custom toolbar.

- On the Tools menu, click Customize.

- Click the Commands tab, and then click Macros in the Categories list. In
the Commands list, drag the Custom button onto a toolbar.

- Right-click the toolbar button, and then click Assign Macro on the
shortcut menu.

- In the Assign Macro box, Click on the name of the macro to assign to the
button, then click OK, and close the Customize dialog box.

8) Now anytime you want to print your Excel worksheet with those headers,
click on your custom button (or run the macro from the Tools>Macro>Macros

(You can also add to or edit your code if you want to make changes, but
that's a whole other email......)

Hope this helps. Let me know how it turns out. ; -)

Patricia Swart
Technical Writer
<The opinions expressed above are my own; not my employer's>


A landmark hotel, one of America's most beautiful cities, and
three and a half days of immersion in the state of the art:
IPCC 01, Oct. 24-27 in Santa Fe.

+++ Miramo -- Database/XML publishing automation. See us at +++
+++ Seybold SFO, Sept. 25-27, in the Adobe Partners Pavilion +++
+++ More info: +++

You are currently subscribed to techwr-l as: archive -at- raycomm -dot- com
To unsubscribe send a blank email to leave-techwr-l-obscured -at- lists -dot- raycomm -dot- com
Send administrative questions to ejray -at- raycomm -dot- com -dot- Visit for more resources and info.


Previous by Author: RE: OT: many thanks to all
Next by Author: RE: H&F in Excel - Yes
Previous by Thread: named destinations from\for bookmarks
Next by Thread: Re: H&F in Excel - Yes

What this post helpful? Share it with friends and colleagues:

Sponsored Ads