How to add for printing and then remove footers?

DarkJester89

Board Regular
Joined
Nov 5, 2017
Messages
94
Office Version
  1. 2016
Platform
  1. Windows
I found this code, which I'm trying to convert to add just a footer, and it'll be on a print button to reflect on pages to be printed.

I only need the footer on a specific sheet, "Sheet1", and then a seperate code to remove footer after print complete.

VBA Code:
Option Explicit

Sub InsertHeaderFooter()

Dim wsAs Worksheet
Application.ScreenUpdating = False
 Each wsInThisWorkbook.Worksheets
                   With ws.PageSetup
                                       .LeftHeader = “Company Name:”
                                       .CenterHeader = “Page &P of &N”
                                       .RightHeader = “Printed &D &T”
                                       .LeftFooter = “Path : “ &ActiveWorkbook.Path
                                       .CenterFooter = “Workbook Name: & F”
                                       .RightFooter = “Sheet: &A”
                  End With
Next ws
Set ws = Nothing
Application.ScreenUpdating = True
End Sub

Thank you in advance.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,774
Office Version
  1. 2010
Platform
  1. Windows
VBA Code:
Sub PrintWithFooter()
  Dim wks           As Worksheet

  Set wks = Worksheets("Sheet1")

  With wks
    .PageSetup.CenterFooter = "whatever"
    .PrintOut
    .PageSetup.CenterFooter = ""
  End With
End Sub
 

DarkJester89

Board Regular
Joined
Nov 5, 2017
Messages
94
Office Version
  1. 2016
Platform
  1. Windows
Hmm, perhaps they shouldn't have been merged in one sub.

VBA Code:
Sub PrintWithFooter()
  Dim wks           As Worksheet

  Set wks = Worksheets("Sheet1")

  With wks
    .PageSetup.CenterFooter = "Whatever"
End Sub

I removed the printout and reset, as i need to reset it at a seperate point in the macro. Thanks though! Got it to work!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,755
Messages
5,574,043
Members
412,565
Latest member
roberttaekim
Top