Excel Copy and Paste Custom Header and Footer

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.
 
C

Chip Pearson

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
 
G

Gord Dibben

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
 
T

tkt_tang

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.
 
T

tkt_tang

Mr. Dibben,

Thank you for your suggestion.

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

Regards.
 
T

tkt_tang

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.
 
M

Max

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 ?
 
R

RagDyeR

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.
 
T

tkt_tang

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.
 
M

Max

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>.

---
 
Joined
Apr 13, 2011
Messages
1
Reaction score
0
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top