Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Oct 2003
    Location
    Calgary, Alberta, Canada
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Custom header - Excel 2010

    Is there a way to take data from one worksheet and put it into the header of another worksheet? And if so, what is it?

    Please and thanks,
    Mary

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Mary,

    Do you want the data linked, e.g. if it changes on the source sheet the header in the other sheet will also change?

    What you want can generally be accomplished two ways.
    1. Copy& Paste {Linked or Not}
    2. VBA code.

    For specific examples we need more information as to what you want to capture and how you would like it formatted.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Lounger
    Join Date
    Oct 2003
    Location
    Calgary, Alberta, Canada
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    custom header

    I want to take the project name from the client info page and put it in the header of the budget summary worksheet and any other worksheet headers. Just to make it more interesting, at the moment it reads:

    Project Name
    &[Tab]

    Once the file is initiated the project name won't change. However as we use the same template every time, I need to be able to change the project name as required. I don't want to copy and paste - time consuming! I'd rather put code in there.

    Please and thanks,
    Mary

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Mary,

    OK here we go.
    First Name the cell containing the Project Name as "ProjectName".
    Next copy the following code into a standard module, you can assign a key to the macro if you wish.
    Code:
    Option Explicit
    
    Sub AutoSheetHdrs()
    
       Dim shtCurSht As Worksheet
       Dim zProjName As String
       
       zProjName = Range("ProjectName").Value
    
       For Each shtCurSht In ActiveWorkbook.Sheets
          shtCurSht.PageSetup.CenterHeader = "Project: " & zProjName
       Next shtCurSht
       
    End Sub    'AutosheetHdrs()
    Of course you're doing this in your template and you'll need to do it for each of your existing workbooks.
    Now just run the Code via Alt+F8 or the key combo you assign {Make sure the project name has been entered}. If you add a sheet just rerun the code.
    Of course you may want to modify the code to format the header with fonts/colors, etc. To do this just start recording a macro and format the header the way you want it then stop recording and copy the appropriate lines into the code above. :cheers
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi
    As RetiredGeek says, if you add a sheet you would need to rerun the code.

    If, like me, you forget a lot, you could instead use this:

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    ActiveSheet.PageSetup.CenterHeader = "Project: " & [ProjName]
    End Sub

    This code assumes you follow RG's advice and named the relevant cell [ProjName].
    You don't need to rerun it if you add sheets.

    This code is added to the ThisWorkbook object (in the left-hand VBA explorer panel - press Ctrl-R if you can't see it).

    The advantage(?) of using the Workbook_BeforePrint method is that it will always set the header even if a User 'changes' or deletes it.

    zeddy

  6. The Following User Says Thank You to zeddy For This Useful Post:

    RetiredGeek (2012-03-01)

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    Thanks I'll add that trick to my bag!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #7
    Lounger
    Join Date
    Oct 2003
    Location
    Calgary, Alberta, Canada
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks guys! Appreciate the assist.
    Mary

Tags for this Thread

Posting Permissions

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