Results 1 to 8 of 8

copying header to all worksheets

This is a discussion on copying header to all worksheets within the Excel Questions forums, part of the Question Forums category; Hi there, I have an Excel file containing 10 worksheets. How can I set up worksheet no1 header info copied ...

  1. #1
    New Member
    Join Date
    Jul 2002
    Posts
    11

    Default copying header to all worksheets

    Hi there,
    I have an Excel file containing 10 worksheets.
    How can I set up worksheet no1 header info copied automatically into worksheets 2 through to 10 so that whatever I type in No 1 header will copy through to the rest.
    Is this possible?
    Thanks
    RC

  2. #2
    Board Regular
    Join Date
    Jul 2003
    Location
    Manchester (UK)
    Posts
    4,432

    Default

    Hi,

    Dont you just need, in sheets 2 to 10 the formula:

    Cell A1: =Sheet1!A1

    and copy this formula across the row.

    HTH

    Alan
    HTH

    Alan

    --------------------------------------------------------
    Vlookup not sufficient? Follow the link for latest version of FuzzyVLookup or RuleLookup .
    Alternatively to compare two worksheets try Compare Two Sheets
    --------------------------------------------------------
    There are 10 kinds of people - those who understand binary and those who don't

  3. #3
    Board Regular
    Join Date
    Nov 2003
    Posts
    230

    Default Re: copying header to all worksheets

    Hi Chevroyd

    You can add a header to a group of sheets in the same workbook by selecting File -> Page setup and choosing the Header/ Footer tab. Then choose the Custom Header button and paste your header into the spaces available.

    There are a bunch of formatting codes available to you when you create a header. Look in the "Formatting Codes for Headers and Footers" in the Excel Help.

    anvil19
    :o

  4. #4
    New Member
    Join Date
    Jul 2002
    Posts
    11

    Default Re: copying header to all worksheets

    al_b_cnu ("In all those old familiar places - That this heart of mine embraces"- da de da - if that rings a bell you are a bit over the hill - like me!)
    and
    anvil19

    thanks board masters and regulars

    thanks for the reply - but this is not the answer to my problem
    I am convinced I have set up a HEADER or FOOTER for a bunch of worksheets in the same file before without having to literally copy the stuff into each sheet's header - (thats why I ask the question) - so I think its possible, but maybe my advanced years ....have distorted my memory banks...anyway..

    (al_b_cnu) - I am not simply trying to refer the cell a1 in worksheets 2 to 10 to cell a1 in worksheet 1 - thats easy - its the HEADER or FOOTER info i want to behave in the same way - see?

    (anvil19) - I am not simply trying to set up the header or footer -
    been there -

    Its the automation bit Im after - if it exists.
    RC

  5. #5
    Board Regular
    Join Date
    Nov 2003
    Posts
    230

    Default Re: copying header to all worksheets

    Howdee Chevroyd

    Yes, I do recognise the ditty, and I'm sorry I didn't get the gist of your problem before (not the being over the hill problem, it's too late for that)

    This is a couple of pieces of code from the 12 October 2002 edition of ExcelTips so with all due consideration to them try it and see if it helps. I changed the first bit of code to just do headers.

    The following two macros can be used to copy headers and footers
    in one simple step.

    All you need to do is display the source worksheet and use the GetHeaders macro. This macro copies the header and footer information to string variables. You can then display, in turn, each worksheet that you want to have the same header and footer and run the DoHeaders macro.

    Code:
    Option Explicit
          
          Dim strHeadLeft As String
          Dim strHeadCenter As String
          Dim strHeadRight As String
          Dim bGotHeaders As Boolean
          
          Sub GetHeaders()
              With ActiveSheet.PageSetup
                  strHeadLeft = .LeftHeader
                  strHeadCenter = .CenterHeader
                  strHeadRight = .RightHeader
                  bGotHeaders = True
              End With
          End Sub
          
          Sub DoHeaders()
              If bGotHeaders Then
                  With ActiveSheet.PageSetup
                      .LeftHeader = strHeadLeft
                      .CenterHeader = strHeadCenter
                      .RightHeader = strHeadRight
                  End With
              Else
                  MsgBox "Select the sheet with the
     headers you want to copy," _
                      & vbCrLf & "then run 'GetHeaders'",
     vbExclamation, _
                      "No Headers In Memory"
              End If
          End Sub
    You could even assign the macros to toolbar buttons, if desired, which can make them even handier for copying headers.

    If you have quite a few worksheets and workbooks into which you want
    the headers and footers copied, there is a different macro approach
    you can use. The following macro will copy the headers and footers
    from the active worksheet to all other worksheets in all other open
    workbooks.

    Code:
    Sub CopyHeaderFooter()
             Dim PS As PageSetup, WB As Workbook, WS As
     Worksheet
             Set PS = ActiveSheet.PageSetup
             For Each WB In Workbooks
               For Each WS In WB.Worksheets
                 With WS.PageSetup
                   .LeftHeader = PS.LeftHeader
                   .CenterHeader = PS.CenterHeader
                   .RightHeader = PS.RightHeader
                   .LeftFooter = PS.LeftFooter
                   .CenterFooter = PS.CenterFooter
                   .RightFooter = PS.RightFooter
                 End With
               Next
             Next
          End Sub
    In other words, if you want to copy headers and footers from the
    current worksheet to 150 other worksheets spread across 15 different
    workbooks, all you need to do is open the 15 workbooks at the same
    time, display the source worksheet, and run the macro


    Good Luck

    anvil19
    :o

  6. #6
    Board Regular
    Join Date
    Sep 2003
    Posts
    88

    Default Re: copying header to all worksheets

    OP,
    Just go to the page with the existing header (the one you want to dispaly elsewhere). Press page setup and then OK.
    Now, choose all the sheets you want your header copied into and..
    press F4.
    OK?
    Cheers.

  7. #7
    New Member
    Join Date
    Jul 2002
    Posts
    11

    Default Re: copying header to all worksheets

    Thanks Lucky Charm

    - thats it ! - blow me down dead easy! I knew Id done it before)

    I still have to do the macro bit anvil19 - have to get out my tutor sometime!
    Thanks anyway
    RC

  8. #8
    Board Regular
    Join Date
    Sep 2003
    Posts
    88

    Default Re: copying header to all worksheets

    Glad you did not require anything easier.
    Would've had to come to your desk and do it myself.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com