 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2006Excel Copy and Paste Custom Header and Footer
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  SignatureCordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com > 1. Enter an Excel Workbook complete with 2 worksheets. > [quoted text clipped - 5 lines] > > 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. 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 >1. Enter an Excel Workbook complete with 2 worksheets. > [quoted text clipped - 4 lines] > >4. 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? SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Mr. Dibben, > [quoted text clipped - 4 lines] > > 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 -- > 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! SignatureHTH, 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>. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.