Excel Copy and Paste Custom Header and Footer

  • Thread starter Thread starter tkt_tang
  • Start date Start date
T

tkt_tang

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
 
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
 
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.
 
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.
Click to expand...

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...
Click to expand...
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 ?
 
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!
=====================================================


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.
 
Glad to hear you finally got it working over there ! Thanks for the
feedback. You've brought new meaning to "E.S.P.". Think you could relax a
little on the formalities, eg salutations (drop the "Mr."), and the
language, though <g>.

---
 
Gord Dibben said:
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.


Excellent solution. Fast, smart simple!!

Thanks Gord
Click to expand...
 
Back
Top