Change headers and footers in excel when printing

Hello,

I have an excel file with headers and footers, i need to print the same excel in two ways, one with the headers anf footers and the other without them.

I have done a macro that takes out the headers and put only de left and right footer, but when I user it, it changes all de print properties of all de pages that i have to print, some of them are landscape and the macro change them.

I need a way to do it, do you know if it is possible?

Thanks, and sorry for may english is not very good

Joana
joanaplicacionsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

NorieAnalyst Assistant Commented:
Joana

Can you post the code of this macro?

Is it meant to change anything apart from the header and footer?

What worksheet(s) are you printing and which ones should have the header/footer?
0
joanaplicacionsAuthor Commented:
Hello Imnorie,

The problem is that i only want to change the header and the footer and it changes other properties, as if it is landscape or not.

This is my code:
Sub Macro1()
'
' Macro1 Macro
'
'Here I select the sheets to print
    Sheets(Array("CARATULA", "index", "PiG", "Detall despeses", "Punt mort", "BalançC", _
        "EOAF", "Anàlisi financera", "Anàlisi rendibilitat", "Anàlisi gestió", _
        "Detall Immobilitzat", "Detall Entitats Públiques", "Càlcul Impost S.", _
        "Informació complementaria", "Resultat-socis")).Select

'here I erase the header (right) and the footer (center)
   
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = "&D"
        .CenterFooter = ""
        .RightFooter = "&P/&N"
    End With
    Application.PrintCommunication = True

' Here I Print the file pdf

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        Nuevo_nombre, Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True

'Here I try to put again the header and footer on all the pages

     Sheets(Array("CARATULA", "index", "PiG", "Detall despeses", "Punt mort", "BalançC", _
        "EOAF", "Anàlisi financera", "Anàlisi rendibilitat", "Anàlisi gestió", _
        "Detall Immobilitzat", "Detall Entitats Públiques", "Càlcul Impost S.", _
        "Informació complementaria", "Resultat-socis")).Select
    ActiveSheet.PageSetup.RightHeaderPicture.Filename = _
        "C:\projecte\comput\Logo_2_x_2.jpg"
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
       .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = "&G"
        .LeftFooter = "&D"
        .CenterFooter = "@pppp"
        .RightFooter = "&P/&N"
    End With
    Application.PrintCommunication = True

End Sub
0
NorieAnalyst Assistant Commented:
Instead of just changing/setting the header and footer change all the settings.

I know there's a lot of them and I'm pretty sure you don't need them all but sometimes with this sort of thing you just have to explicitly give all the arguments
0
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

joanaplicacionsAuthor Commented:
I have tried this solution but is not valid for me for 2 reasons:

1.- In the macro I will have to go sheet by sheet to fix the parameters, as i have some pages that are landscape and others are portrait, and the margins and properties are diferent.

2.- If I chage a print parameter in a sheet I will be forced to go to the macro and change it, also, manually each time I make changes.

I was looking for another solution, that let me take out the headers and footers without changing the rest of parameters.
0
NorieAnalyst Assistant Commented:
Well I can't properly test, but the code you posted appears to work for me when I check in Print Preview.

So my suggestion of using all the parameters was obviously wrong and I apologize.
0
joanaplicacionsAuthor Commented:
No problem,

Any other idea would be greatly appreciated.

Thanks for looking my problem
0
NorieAnalyst Assistant Commented:
What happens if you don't turn use PrintCommunication?

When I tried the code at first I didn't use that and the code worked, but now when I do use it all the pages do change their page setup.

I know it's meant to speed up things up when you are using page setup, but I didn't notice a difference.
0
joanaplicacionsAuthor Commented:
Hello Imnorie,

I have tested taking out Application.PrintCommunication = False/true, in that case the landscape sheets remain as they are but it doesn't take out the logo and the center footer. It remains as it is.
0
NorieAnalyst Assistant Commented:
I can see where your code adds a logo but not where it removes it
0
joanaplicacionsAuthor Commented:
Imnorie,

At the end I have solved the problem, in fact the two problems:

1.- I try to do the changes selecting all the sheets at the same time, and this didn't work properly
2.- As you said I have to take out Application.PrintCommunication = False/true becouse it makes the function ActiveSheet.PageSetup not work properly

Now I repeat the same code sheet by sheet and it respect the other properties.

    Sheets("index").Select
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = "&D"
        .CenterFooter = ""
        .RightFooter = "&P/&N"
    End With
    Sheets("PiG").Select
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = "&D"
        .CenterFooter = ""
        .RightFooter = "&P/&N"
    End With
........

Thankyou very much, having someone working with me has helped me a lot.
0
NorieAnalyst Assistant Commented:
I was going to suggest going through the sheets with a loop to set the page setup for each but got caught up in my original idea of setting all the parameters and how to implement it in the loop.

Then I went back to the original code and it worked but I only tried it on a few sheets with simple formatting and no logo.

Anyway, you got it to work.:)
0
NorieAnalyst Assistant Commented:
With my help.:)
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
joanaplicacionsAuthor Commented:
Yes , without you I wouldn't get the solution
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
VB Script

From novice to tech pro — start learning today.