如果工作表上有数据，则创建新工作簿

Question

仅当工作表上有数据时才尝试创建新工作簿，但我收到"CountA“部分的错误"Argument not optional”

编辑新代码：

Sub teststs()

If WorksheetFunction.CountA(Worksheets("Sheet2").Range("A1:AY300")) = 0 And Worksheets("Sheet2").Shapes.Count = 0 Then
    Worksheets("Sheet2").Delete

    Else
    Dim sWorkbook As Workbook

    'Create New Workbook
    Set sWorkbook = Workbooks.Add

    'Save Above Created New Workbook
    sWorkbook.SaveAs Filename:="C:\CODE\11 Production.xlsx"
    End If

If WorksheetFunction.CountA(Worksheets("Sheet3").Range("A1:AY300")) = 0 And Worksheets("Sheet3").Shapes.Count = 0 Then
    Worksheets("Sheet3").Delete

    Else
    Dim sWorkbook1 As Workbook

    'Create New Workbook
    Set sWorkbook1 = Workbooks.Add

    'Save Above Created New Workbook
    sWorkbook1.SaveAs Filename:="C:\CODE\22 Production.xlsx"
    End If

If WorksheetFunction.CountA(Worksheets("Sheet4").Range("A1:AY300")) = 0 And Worksheets("Sheet4").Shapes.Count = 0 Then
    Worksheets("Sheet4").Delete

    Else
    Dim sWorkbook2 As Workbook

    'Create New Workbook
    Set sWorkbook2 = Workbooks.Add

    'Save Above Created New Workbook
    sWorkbook2.SaveAs Filename:="C:\CODE\33 Production.xlsx"
    End If

If WorksheetFunction.CountA(Worksheets("Sheet5").Range("A1:AY300")) = 0 And Worksheets("Sheet5").Shapes.Count = 0 Then
    Worksheets("Sheet5").Delete

    Else
    Dim sWorkbook3 As Workbook

    'Create New Workbook
    Set sWorkbook3 = Workbooks.Add

    'Save Above Created New Workbook
    sWorkbook3.SaveAs Filename:="C:\CODE\44 Production.xlsx"
    End If

If WorksheetFunction.CountA(Worksheets("Sheet6").Range("A1:AY300")) = 0 And Worksheets("Sheet6").Shapes.Count = 0 Then
    Worksheets("Sheet6").Delete

    Else
    Dim sWorkbook4 As Workbook

    'Create New Workbook
    Set sWorkbook4 = Workbooks.Add

    'Save Above Created New Workbook
    sWorkbook4.SaveAs Filename:="C:\CODE\55 Production.xlsx"
    End If



End Sub

Answer 1

试着这样做

Sub tetstststs()

If WorksheetFunction.CountA(Worksheets("Sheet1").Range("A1:M10")) = 0 And Worksheets("Sheet1").Shapes.Count = 0 Then
    Worksheet("Sheet1").Delete

    Else
    Dim sWorkbook As Workbook

    'Create New Workbook
    Set sWorkbook = Workbooks.Add

    'Save Above Created New Workbook
    sWorkbook.SaveAs Filename:="C:\CODE\Test.xlsx"
    End If

End Sub

祝好运