1. Enter an Excel Workbook complete with 2 worksheets.

2. Sheet1 has a set of custom header and footer.

3. Please show that the custom header and footer of Sheet1 could be
Copy-and-Paste'd onto Sheet2.

4. Regards.

Try the following code:

Worksheets("Sheet2").PageSetup.LeftHeader = _
Worksheets("Sheet1").PageSetup.LeftHeader


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 1. Enter an Excel Workbook complete with 2 worksheets.
>
> 2. Sheet1 has a set of custom header and footer.
>
> 3. Please show that the custom header and footer of Sheet1
> could be
> Copy-and-Paste'd onto Sheet2.
>
> 4. Regards.
>

Select Sheet1 then CTRL + click Sheet2

Do your custom setup on Sheet1 and will be done to Sheet2 also.

DO NOT FORGET to ungroup the sheets after this. What you do to one will be done
to both.


Gord Dibben MS Excel MVP

On 10 Jan 2006 16:39:36 -0800, (E-Mail Removed) wrote:

>1. Enter an Excel Workbook complete with 2 worksheets.
>
>2. Sheet1 has a set of custom header and footer.
>
>3. Please show that the custom header and footer of Sheet1 could be
>Copy-and-Paste'd onto Sheet2.
>
>4. Regards.

Mr. Pearson,

Thank you for your suggestion.

Tested the given code in Workbook Module under Workbook_SheetChange
Event ; it has worked.

However, I'm afraid that the Macro would cause errors when the names of
the corresponding worksheets are changed.

Regards.

Mr. Dibben,

Thank you for your suggestion.

However, the given method does not appear to bring about the desired
effects.

Regards.

Exactly what effect DOES it bring about?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Mr. Dibben,
>
> Thank you for your suggestion.
>
> However, the given method does not appear to bring about the desired
> effects.
>
> Regards.
>

There appears to be nothing of the effect at all (meaning that none of
the copy-and-paste from Sheet1 to sheet2 with respect to the custom
header and footer are effected).

Regards.

I followed Gord's steps below and it works fine

> Select Sheet1 then CTRL + click Sheet2
> Do your custom setup on Sheet1 and will be done to Sheet2 also.

With the 2 sheets grouped, I went through:
File > Page Setup > Header/Footer tab
& did the Custom Header & Footer > OK'ed out

> DO NOT FORGET to ungroup the sheets after this...
I remembered to do this
(Right-clicked on either of Sheet1/2 > Ungroup Sheets)

Then, when I selected each of the 2 sheets in turn, and did:
File > Print preview
I could see that the custom headers/footers
were correctly applied on both sheets

Perhaps you might want to try it again ?
--
Max, Singapore GMT+8, xl97
Samples archive at: http://savefile.com/projects/236895
xdemechanik
--
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> There appears to be nothing of the effect at all (meaning that none of
> the copy-and-paste from Sheet1 to sheet2 with respect to the custom
> header and footer are effected).
>
> Regards.
>

Gord's suggestion is a time proven (across all versions) procedure for
duplicating headers and footers.

Perhaps you don't understand the 'Grouping' of worksheets, which is
necessary for this procedure to work.

When sheets are grouped, whatever is done to the "main" sheet, will be
duplicated to all the other sheets in the group.

When sheets are 'Grouped', the tabs of each sheet in the group is colored
white, with the main sheet also in bold.
In the title bar, appended to the name of the WB, you'll see "[Group]".

To group sheets, with the main sheet (sheet to be revised and copied from)
in focus, hold down <Ctrl>, and click in the tabs of each sheet that you
wish to add to the group.
If the sheets to group are contiguous, you can hold down <Shift>, and click
in the last sheet, to include them all.

Now, while these sheets are grouped, make your revisions and/or create your
headers.
Everything will be duplicated in/on the rest of the sheets in the group.

When you're done, don't forget to "un-group" the sheets by clicking in a tab
of a sheet not in the group, OR, right click in a tab of a grouped sheet and
choose "Ungroup Sheets".

However, lets say that you *already* have an existing sheet , with the
headers and/or footers already created.

Click in the tab of this 'master' sheet so that it is the sheet in focus.
Add the other sheets to the group as described above, making *sure* that the
'main' sheet is in bold (in focus).

Now, click in
<File> <PageSetUp> <OK>

Your headers and footers are now duplicated in all the sheets in the group.

Ungroup your sheets!
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
There appears to be nothing of the effect at all (meaning that none of
the copy-and-paste from Sheet1 to sheet2 with respect to the custom
header and footer are effected).

Regards.

Mr. RagDyeR and Mr.Max,

Feelingly, I'm blessed for having received your dissertations of the
subject matter. Very grateful indeed to have benefited from the Eminent
Solution Providers.

The following is given for the benefits of those interested checking-in
around this neighbourhood :-

Selection, Grouping and hence UnGrouping of the worksheets are
apparently piece-of-cake. The avenue en-route to custom header and
footer is "Page Setup" ; this portal is presumably critical. Consider
that custom header and footer are also accessible via "Print Preview" ;
but the results are entirely different (depending on the paths taken).

Regards.