Solved

Excel - Page layout - Margins

Posted on 2016-11-16
7
63 Views
Last Modified: 2016-11-17
Hi Experts,
I am running Office 365 Pro Plus.

I am changing a lot of workbooks that have headers and footers in every one. I want to completely remove the header footer.
The margins are also very messed up.

My question is: Is there a way to save the page layout and header footer setup, so I can quickly change every single workbook I am working on??

I should clarify that I DO know how to do these steps to each WB individually with no problem.

Thanks so much.
0
Comment
Question by:chris pike
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 

Author Comment

by:chris pike
ID: 41890296
I tried creating a macro to delete the header footer, but it is not working.
Hmmmm
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41890999
Can you elaborate "not working"?
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41891824
Can you clarify your repeated reference to "Header Footer"? Do you mean Header OR Footer OR Both.

If you use the VB Recorder to do the changes to one workbook, post the result here and we can look at it to amend so that it can be used from your Personal workbook and applied to any active workbook.
1
Technology Partners: 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!

 
LVL 20

Expert Comment

by:Roy_Cox
ID: 41891845
If you are using the same layout multiple times then create a Template.

This will change every sheet in the workbook. Are all the workbooks to change in one folder?


Option Explicit

Sub HeadersandFooters()
'


    Dim ws As Excel.Worksheet

    For Each ws In ThisWorkbook.Worksheets

        With ws.PageSetup

            ''/// clear headers & footers
            .LeftHeader = ""
            .CenterHeader = ""
            .RightHeader = ""
            .LeftFooter = ""
            .CenterFooter = ""
            .RightFooter = ""

            ''/// set the margins
            ''/// tip: record a macro to get the exact margin sizes
            .LeftMargin = Application.InchesToPoints(0.884241878403837)
            .RightMargin = Application.InchesToPoints(0.708771417322834)
            .TopMargin = Application.InchesToPoints(0.884241878403837)
            .BottomMargin = Application.InchesToPoints(0.480441181102372)
            .HeaderMargin = Application.InchesToPoints(0.187840383700787)
            .FooterMargin = Application.InchesToPoints(0.118110237220472)

        End With
    Next ws

End Sub

Open in new window

0
 
LVL 20

Accepted Solution

by:
Roy_Cox earned 500 total points
ID: 41891855
This will loop through a sub folder called data and change the margins, etx
Option Explicit


Sub ResetAll()
    Dim oWbk As Workbook
    Dim oWs As Worksheet
    Dim sFil As String, sPath As String

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
        '   On Error GoTo exithandler
        ' assumes workbooks are in a sub folder named "Data"
        sPath = ThisWorkbook.Path & Application.PathSeparator & "Data"
        ChDir sPath
        sFil = Dir("*.xl**")    'file type
        Do While sFil <> ""    'will start LOOP until all files in folder sPath have been looped through

           For Each oWs In ActiveWorkbook.Worksheets

        With oWs.PageSetup

            ''/// clear headers & footers
            .LeftHeader = ""
            .CenterHeader = ""
            .RightHeader = ""
            .LeftFooter = ""
            .CenterFooter = ""
            .RightFooter = ""

            ''/// set the margins
            ''/// tip: record a macro to get the exact margin sizes
            .LeftMargin = Application.InchesToPoints(0.884241878403837)
            .RightMargin = Application.InchesToPoints(0.708771417322834)
            .TopMargin = Application.InchesToPoints(0.884241878403837)
            .BottomMargin = Application.InchesToPoints(0.480441181102372)
            .HeaderMargin = Application.InchesToPoints(0.187840383700787)
            .FooterMargin = Application.InchesToPoints(0.118110237220472)

        End With
    Next oWs
            oWbk.Close True     'close source workbook
            sFil = Dir
        Loop    ' End of LOOP
        
exithandler:
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    End With
End Sub

Open in new window

0
 

Author Closing Comment

by:chris pike
ID: 41891937
Thanks I can work with this.
Thanks Roy
0
 
LVL 20

Expert Comment

by:Roy_Cox
ID: 41892530
Pleased to help. Post back if you need further help.
0

Featured Post

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
If you troubleshoot Outlook for clients, you may want to know a bit more about the OST file before doing your next job. IMAP can cause a lot of drama if removed in the accounts without backing up.
This Experts Exchange lesson shows how to use VBA to loop through rows in Excel.  In order to sort, filter, and use database features, there needs to be a value in each column for every row. When data arrives with values missing, code to copy values…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question