Solved

Excel - Page layout - Margins

Posted on 2016-11-16
7
56 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 19

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 19

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 19

Expert Comment

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

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
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…

726 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