EXCEL Workbook Options

Financial workbooks, multiple tabs, cannot find a method for allowing print titles to print on all pages for the whole workbook. Only info I can find is that you need to open each tab - painful
archdioceseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
This adds headers to all sheets.

Sub AddHeaders()
Dim sh As Worksheet

    Application.ScreenUpdating = False
         
    For Each sh In ActiveWorkbook.Worksheets
        sh.Activate
        ActiveWindow.View = xlPageLayoutView
    
       ' Seting the PrintCommunication property to False speeds up the
       ' Print execution of code that sets PageSetup Properties. Setting the
       ' PrintCommunication property to True after setting properties to
       ' commits all cached PageSetup commands.
        Application.PrintCommunication = False
        
        With ActiveSheet.PageSetup
            .LeftHeader = ""
            .CenterHeader = "My Header"
            .RightHeader = ""
        End With
        Application.PrintCommunication = True
    
    Next sh
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ProfessorJimJamCommented:
here is non-macro solution if it is single workbook with multiple sheets
Just go to the page with the existing header (the one you want to display elsewhere). Press page setup and then OK.  Now, choose all the sheets you want your header copied into and then  press F4


if you have multiple workbooks with multiple worksheets, then use the below macro.

Sub CopyHeaderFooter()
    Dim PS As PageSetup
    Dim WB As Workbook
    Dim 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
    MsgBox "Completed!"
End Sub

Open in new window

0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Alternately, right-click on a sheet tab and choose "Select all sheets", or click on the first sheet tab then shift-click on the last sheet tab (if the ones you want are next to each other) or ctrl-click sheet tabs to toggle selection of them one at a time.  Whatever you set will happen to all selected sheets.

When you are done making changes, right-click on a selected sheet and choose "Ungroup Sheets" or click on a sheet that is not part of the selection.
0
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.