Excel - Page layout - Margins

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.
chris pikeAsked:
Who is Participating?
 
Roy CoxGroup Finance ManagerCommented:
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
 
chris pikeAuthor Commented:
I tried creating a macro to delete the header footer, but it is not working.
Hmmmm
0
 
Saqib Husain, SyedEngineerCommented:
Can you elaborate "not working"?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Rob HensonFinance AnalystCommented:
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
 
Roy CoxGroup Finance ManagerCommented:
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
 
chris pikeAuthor Commented:
Thanks I can work with this.
Thanks Roy
0
 
Roy CoxGroup Finance ManagerCommented:
Pleased to help. Post back if you need further help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.