excel - 如何计算张数是一种数字方式，无论名称和操作如复制或移动

Question

该代码及其目的是(下面的代码，sub ed)查看工作簿/文件名为“PR11_P3.xlsm”的窗口，然后计数到最后一张表，然后选择它。将其移动(而不是复制)到新文件中并将其保存到路径/目标。这按预期工作。
当没有最后一张纸时，我遇到了错误，这是我现在发布的这个问题的目标，最好的情况是(我认为做一个 IF)如果有不止一张纸，执行 cod 为是，但如果只有一张或多于 2 张，则复制当前工作表而不是移动到新文件并另存为，关闭文件。
有什么建议么？基本上我想确保没有代码输出错误原因导致纸张计数的情况，并且由于第二张纸张的名称总是不同，所以这是我唯一合乎逻辑的方法，但我认为它非常困惑，如前所述不起作用 - 我需要专家指导。
感谢帮助!
我的代码

Sub ED() 'Export last sheet into new file in the background, save as and close

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

        Windows("PR11_P3.xlsm").Activate
        Sheets(Sheets.Count).Select
        Sheets(Sheets.Count).Move
        ActiveWorkbook.SaveAs Filename:="C:\Temp\PR\Export\Bok1.xlsx", FileFormat:= _
            xlOpenXMLWorkbook, CreateBackup:=False
        ActiveWindow.Close

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

我认为我的代码应该是这样，而且我在这里做得不好，因为有错误并且它不能正常运行。

Sub ED_IF() 

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
If Sheets(Sheets.Count) =< 3 Then 'not more than two sheets
Windows("PR11_P3.xlsm").Activate
Sheets(Sheets.Count).Select
Sheets(Sheets.Count).Move
ActiveWorkbook.SaveAs Filename:="C:\Temp\PR\Export\Bok1.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Else
If Sheets(Sheets.Count) = 1 or => 2 Then 'basically if there is only one sheet or more than 2 sheets, so it can be any number above 2
Windows("PR11_P3.xlsm").Activate
Sheets(Sheets.Count).Copy
ActiveWorkbook.SaveAs Filename:="C:\Temp\PR\Export\Bok1.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
End If
End If

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Answer 1

像这样的东西:

Sub ED() 'Export last sheet into new file in the background, save as and close
    Dim wb As Workbook, ws As Worksheet, wbNew As Workbook, nSheets As Long
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Set wb = Workbooks("PR11_P3.xlsm")   'or ThisWorkbook?
    nSheets = wb.Worksheets.Count
    Set ws = wb.Worksheets(nSheets)  'sheet to copy/move
    
    With Workbooks.Add(xlWBATWorksheet) 'add a new workbook with 1 sheet
        Select Case nSheets
            Case 1: ws.Copy after:=.Sheets(1)
            Case Else: ws.Move after:=.Sheets(1)
        End Select ' fixed
        .Sheets(1).Delete 'remove the empty sheet
        .SaveAs Filename:="C:\Temp\PR\Export\Bok1.xlsx", _
             FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        .Close
    End With

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub