Solved

Excel - Page layout - Margins

Posted on 2016-11-16
7
42 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 32

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
Make managing Office 365 email signatures a breeze

Are you using Office 365? Having trouble trying to set up email signatures for your users? Getting stressed out managing multiple signatures? Need an easier way to manage? We have a solution for you, try the most-user friendly and powerful signature management tool on the market.

 
LVL 17

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 17

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 17

Expert Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

We are happy to announce a brand new addition to our line of acclaimed email signature management products – CodeTwo Email Signatures for Office 365.
Cloud-based technologies and services will continue to grow in popularity in 2017 thanks to the simple, scalable and cost-effective solutions they deliver. Here are three areas where cloud adoption is poised to really take off.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now