基于日期自动保存并新建Excel工作表的VBA代码优化需求

核心需求 #

• 打开工作簿时自动检查当前日期
• 若为每月1日：
  1. 将当前月度工作表以上月日期命名导出为PDF
  2. 基于该工作表创建新模板（保留公式、格式、单元格尺寸，仅清除当月销售数据）并替换原工作表

完整实现代码 #

步骤1：添加工作簿打开触发逻辑 #

将以下代码粘贴到ThisWorkbook模块中（右键工作簿→查看代码→选择ThisWorkbook）：

Private Sub Workbook_Open()
    ' 检查是否为每月1日
    If Day(Date) = 1 Then
        ' 执行PDF导出（以上月命名）
        ExportSheetAsLastMonthPDF
        ' 创建新模板并替换原工作表
        CreateCleanTemplateReplaceOriginal
    End If
End Sub

步骤2：修改PDF导出宏（适配上月命名） #

替换原WorksheetExport为以下代码：

Sub ExportSheetAsLastMonthPDF()
    Dim wsToSave As Worksheet
    Dim filePathToSave As String
    Dim lastMonthName As String
    
    Application.ScreenUpdating = False
    
    ' 获取上月的mmyyyy格式命名
    lastMonthName = Format(DateAdd("m", -1, Date), "_mmyyyy")
    Set wsToSave = ThisWorkbook.Worksheets("bqtest")
    filePathToSave = "C:\Users\thelo\Desktop\Quail Stuff\TEST\"
    
    ' 导出PDF，文件名包含上月日期
    wsToSave.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=filePathToSave & wsToSave.Name & lastMonthName & ".pdf"
        
    Application.ScreenUpdating = True
End Sub

步骤3：修改模板创建宏（清除销售数据并替换原表） #

替换原CreateNewSheetWithMonth为以下代码：

Sub CreateCleanTemplateReplaceOriginal()
    Dim wsOriginal As Worksheet
    Dim wsNewTemplate As Worksheet
    Dim salesDataRange As Range
    
    ' 定义原工作表
    Set wsOriginal = ThisWorkbook.Worksheets("bqtest")
    
    ' 复制原工作表到新工作表
    wsOriginal.Copy After:=ThisWorkbook.Sheets(1)
    Set wsNewTemplate = ActiveSheet
    
    ' --- 关键：替换为你的实际销售数据范围 ---
    ' 示例：清除B2:C100区域的内容，保留格式和公式
    Set salesDataRange = wsNewTemplate.Range("B2:C100")
    salesDataRange.ClearContents
    
    ' 删除原工作表，将新模板重命名为原名称
    Application.DisplayAlerts = False ' 关闭删除提示
    wsOriginal.Delete
    wsNewTemplate.Name = "bqtest"
    Application.DisplayAlerts = True
End Sub

关键说明 #

• 日期触发：通过Workbook_Open()事件实现启动时自动检查，Day(Date) = 1判断是否为每月1日
• 上月命名：用DateAdd("m", -1, Date)获取上月日期，格式化后作为PDF文件名后缀
• 数据清除：需根据实际销售数据位置修改salesDataRange，确保仅清除需要更新的数值，保留公式、格式和单元格尺寸
• 表替换逻辑：通过复制→清数据→删原表→重命名的流程，实现无缝替换原工作表

内容的提问来源于stack exchange，提问作者Dzewa