VBA代码：自定义页眉页脚打印内容

fanjy

发布于 2023-12-26 16:13:16

5590

发布于 2023-12-26 16:13:16

文章被收录于专栏：完美Excel

标签：VBA

要在工作表打印时每页上打印不同的页眉和页脚，可以使用VBA代码来实现。Excel中的页眉和页脚各分为三部分：LeftHeader、CenterHeader、RightHeader，以及LeftFooter、CenterFooter和RightFooter，可以根据需要更改使用或隐藏其中的一些。

下面是一些自定义工作表页眉和页脚的VBA代码。

仅打印指定工作表第一页左侧页脚

Public Sub FirstPageFooterOnly_OneSheet()
 Dim sFooter As String
 With Sheets("Sheet1")
   sFooter = .PageSetup.LeftFooter
   .PrintOut From:=1, To:=1
   .PageSetup.LeftFooter = ""
   .PrintOut From:=2
   .PageSetup.LeftFooter = sFooter
 End With
End Sub

打印指定工作表除第一页外所有其他页的左侧页脚

Public Sub NoFirstPageFooter_OneSheet()
 Dim sFooter As String
 With Sheets("Sheet1")
   sFooter = .PageSetup.LeftFooter
   .PageSetup.LeftFooter = ""
   .PrintOut From:=1, To:=1
   .PageSetup.LeftFooter = sFooter
   .PrintOut From:=2
 End With
End Sub

仅打印所有工作表第一页的左侧页脚

Public Sub FirstPageFooterOnly_AllSheets()
 Dim wsSheet As Worksheet
 Dim sFooter As String
 For Each wsSheet In Worksheets
   With wsSheet
     sFooter = .PageSetup.LeftFooter
     .PrintOut From:=1, To:=1
     .PageSetup.LeftFooter = ""
     .PrintOut From:=2
     .PageSetup.LeftFooter = sFooter
   End With
 Next wsSheet
End Sub

打印所有工作表除第一页外所有其他页的左侧页脚

Public Sub NoFirstPageFooter_AllSheets()
 Dim wsSheet As Worksheet
 Dim sFooter As String
 For Each wsSheet In Worksheets
   With wsSheet
     sFooter = .PageSetup.LeftFooter
     .PageSetup.LeftFooter = ""
     .PrintOut From:=1, To:=1
     .PageSetup.LeftFooter = sFooter
     .PrintOut From:=2
   End With
 Next wsSheet
End Sub

下面的代码要放置在ThiwWorkbook代码模块中。

仅自动打印指定工作表第一页左侧页脚

Private Sub Workbook_BeforePrint(Cancel As Boolean)
 Dim wsSheet As Worksheet
 Dim sFooter As String
 Cancel = False
 Application.EnableEvents = False
 For Each wsSheet In ActiveWindow.SelectedSheets
   With wsSheet
     If .Name = "Sheet1" Then
       sFooter = .PageSetup.LeftFooter
       .PrintOut From:=1, To:=1
       .PageSetup.LeftFooter = ""
       .PrintOut From:=2
       .PageSetup.LeftFooter = sFooter
     Else
       .PrintOut
     End If
   End With
 Next wsSheet
 Application.EnableEvents = True
End Sub

自动打印指定工作表除第一页外所有其他页的左侧页脚

Private Sub Workbook_BeforePrint(Cancel As Boolean)
 Dim wsSheet As Worksheet
 Dim sFooter As String
 Cancel = False
 Application.EnableEvents = False
 For Each wsSheet In ActiveWindow.SelectedSheets
   With wsSheet
     If .Name = "Sheet1" Then
       sFooter = .PageSetup.LeftFooter
       .PageSetup.LeftFooter = ""
       .PrintOut From:=1, To:=1
       .PageSetup.LeftFooter = sFooter
       .PrintOut From:=2
     Else
       .PrintOut
     End If
   End With
 Next wsSheet
 Application.EnableEvents = True
End Sub

自动打印选定工作表第一页的左侧页脚

Private Sub Workbook_BeforePrint(Cancel As Boolean)
 Dim wsSheet As Worksheet
 Dim sFooter As String
 Cancel = False
 Application.EnableEvents = False
 For Each wsSheet In ActiveWindow.SelectedSheets
   With wsSheet
     sFooter = .PageSetup.LeftFooter
     .PrintOut From:=1, To:=1
     .PageSetup.LeftFooter = ""
     .PrintOut From:=2
     .PageSetup.LeftFooter = sFooter
   End With
 Next wsSheet
 Application.EnableEvents = True
End Sub

自动打印选定工作表除第一页外其他页的左侧页脚

Private Sub Workbook_BeforePrint(Cancel As Boolean)
 Dim wsSheet As Worksheet
 Dim sFooter As String
 Cancel = False
 Application.EnableEvents = False
 For Each wsSheet In ActiveWindow.SelectedSheets
   With wsSheet
     sFooter = .PageSetup.LeftFooter
     .PageSetup.LeftFooter = ""
     .PrintOut From:=1, To:=1
     .PageSetup.LeftFooter = sFooter
     .PrintOut From:=2
   End With
 Next wsSheet
 Application.EnableEvents = True
End Sub

注：代码整理自mcgimpsey.com，供有兴趣的朋友参考。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2023-12-21，如有侵权请联系 cloudcommunity@tencent.com 删除

vba