Private Sub Workbook_Open()
    Dim latestSheetName As String, monthstr As String
    Dim currentMonth As Integer, currentYear As Integer, latestYear As Integer, latestMonth As Integer, lMonth As Integer, lYear As Integer
    Dim lastRow As Long
    Dim sheet As Worksheet
    
    ' Get the current year and month
    currentMonth = Format(Date, "mm")
    currentYear = Format(Date, "yyyy")
    
    If Len(CStr(currentMonth)) < 2 Then
        monthstr = "0" & currentMonth
    Else
        monthstr = currentMonth
    End If
    
    ' Loop through all sheets and find the latest one
    lMonth = 0
    lYear = 0
    For Each sheet In ThisWorkbook.Sheets
        If sheet.Name Like "bqtest_######" Then
            latestYear = Right(sheet.Name, 4)
            latestMonth = Mid(sheet.Name, 8, 2)
            If latestMonth > lMonth Or latestYear > lYear Then
                latestSheetName = sheet.Name
                lYear = latestYear
                lMonth = latestMonth
            End If
        End If
    Next sheet
    
    ' Check if the latest sheet is older than the current year and month
    If currentYear = lYear Or currentMonth > lMonth Then
        Worksheets(latestSheetName).Copy After:=Worksheets(ThisWorkbook.Sheets.Count)
            ActiveSheet.Name = "bqtest_" & monthstr & currentYear
            lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
            'clear range starting at A2 to last row change accordingly
            ActiveSheet.Range("A2").Resize(lastRow - 1).ClearContents
    ElseIf currentYear > lYear Then
        Worksheets(latestSheetName).Copy After:=Worksheets(ThisWorkbook.Sheets.Count)
           ActiveSheet.Name = "bqtest_" & monthstr & currentYear
            lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
            'clear range starting at A2 to last row change accordingly
            ActiveSheet.Range("A2").Resize(lastRow - 1).ClearContents
    End If
End Sub

字符串

Sub CreateNewSheetWithMonth()

    Dim ws As Worksheet
    Dim strToday As String
    Dim bCheck As Boolean
    Const strTARGET_PATH = "C:\Users\thelo\Desktop\Quail Stuff\TEST\"

    'activate the worksheet bqtest... (the workbook may contain other sheets, too)
    For Each ws In ThisWorkbook.Worksheets 'check each worksheet
        If InStr(ws.Name, "bqtest") = 1 Then 'if sheet name starts with "bqtest"
            ws.Activate         'activate it
        End If
    Next ws

    'check if a worksheet exists for this month
    strToday = Format(Date, "_mmyyyy") 'format today's date
    On Error Resume Next        'avoid error if not existing
    bCheck = Len(Sheets("bqtest" & strToday).Name) > 0  'check if exists
    On Error GoTo 0             'restore error checking
    
    'take action if new month
    If bCheck = False Then      'skip if worksheet exists
        'save last month's worksheet as PDF
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
          Filename:=strTARGET_PATH & ActiveSheet.Name & ".pdf"
        
        'copy ActiveSheet and paste it after ActiveSheet, activate it
        ActiveSheet.Copy After:=ActiveSheet
        ActiveSheet.Name = "bqtest" & strToday 'rename to this month
    End If

End Sub

字符串
这基本上是你的代码，删除了一些不必要的代码。
我已经更改了它，以便它在其名称下导出上个月的工作表，例如2023年3月的bqtest_032003.pdf。
在你的代码中，没有删除旧月份的工作表。如果你想让Excel自动删除，删除行ActiveSheet.Copy After:=ActiveSheet。然后旧工作表被重命名为新月份，而不是复制。
你提到你想在新的一个月里删除销售数据，要么你手工做，要么你自己写代码，因为我不知道这些数据写在哪里。
希望这次能成功。