使用vba代码按图纸名称更改和定义代码

尚不清楚我们如何知道哪些工作表应该导出到它们自己的工作簿中。这个答案假设您想要导出每个名称以“Lista_”开头的工作表,这样就可以导出“Lista_AA”、“Lista_BB”而不是“膳食登记”

Sub EXCELS()
    'Create excel files
    
    Dim i As Integer
    Dim name_file As String
    
    For i = 1 To Worksheets.Count
        name_file = Sheets(i).Name
        If Left(name_file, 6) = "Lista_" Then
            Worksheets(i).Copy
            
            With ActiveWorkbook
                .SaveAs Filename:="C:\Users\marya\OneDrive - Desktop\Cantina\listas\" & _
                name_file & ".xlsx", FileFormat:=xlOpenXMLWorkbook
                .Close SaveChanges:=False
            End With
        End If
    Next i

End Sub

如果工作表名称有不同的模式,或者如果您想指定要导出的工作表名称列表,请在下面发表评论,我将修改或做出新的回答。

下面是一个版本,它将覆盖具有相同名称的现有工作簿,而不提示用户

Sub EXCELS()
    'Create excel files
    
    Dim i As Integer
    Dim name_file As String
    Dim file_path as String

    Application.DisplayAlerts = False

    For i = 1 To Worksheets.Count
        name_file = Sheets(i).Name
        If Left(name_file, 6) = "Lista_" Then
            Worksheets(i).Copy
            file_path = "C:\Users\marya\OneDrive - Desktop\Cantina\listas\" & name_file & ".xlsx"

            ' try to delete a file that has the same name as the one we are about to save  
            on error resume next
            kill file_path
            on error goto 0     

            debug.print file_path
            With ActiveWorkbook
                .SaveAs Filename:=file_path, FileFormat:=xlOpenXMLWorkbook
                .Close SaveChanges:=False
            End With
        End If
    Next i

End Sub