Solved

Excel - Page layout - Margins

Posted on 2016-11-16
7
52 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
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 18

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 18

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 18

Expert Comment

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

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

Question has a verified solution.

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

Find out what the Office 365 disclaimer function is, why you would use it and its limited ability to create Office 365 signatures.
Adoption of Microsoft’s Enterprise Mobility and Security solution and Office 365 will re-order the File Sync and Share market Microsoft has stated that its Enterprise Mobility + Security (EMS) is the fastest growing product in the history of the …
how to add IIS SMTP to handle application/Scanner relays into office 365.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

839 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