Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • Last Modified:

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
0
joanaplicacions
Asked:
joanaplicacions
  • 7
  • 6
1 Solution
 
NorieCommented:
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
 
NorieCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
NorieCommented:
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
 
NorieCommented:
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
 
NorieCommented:
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
 
NorieCommented:
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
 
NorieCommented:
With my help.:)
0
 
joanaplicacionsAuthor Commented:
Yes , without you I wouldn't get the solution
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now