Change and define code by sheets names with vba code-CodePudding

Currently I have this code below:

 Sub EXCELS()

'Create excel files

 Dim i As Integer
 Dim name_file As String
 For i = 5 To Sheets.Count
 name_file = Sheets(i).Name

 Worksheets(i).Copy

 With ActiveWorkbook
 .SaveAs Filename:="C:\Users\marya\OneDrive - Desktop\Cantina\listas" & "\" & 
 name_file & ".xlsx", FileFormat:=xlOpenXMLWorkbook
 .Close SaveChanges:=False
 End With
 Next i

 End Sub

I would like to change and define this code below by sheets names, as "Lista_AA", "Lista_BB". Instead of creating excel files from sheet 5, create excel files that contain the names "Lista_AA", "Lista_BB"...

For i = 5 To Sheets.Count

Error:

CodePudding user response：

It's not clear how we know which sheets should be exported to their own workbooks. This answer assumes that you want to export each sheet that has a name starting with "Lista_", so that would export "Lista_AA", "Lista_BB" but not "Meal_Register"

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

If there is a different pattern for the sheet names, or if you want to specify a list of sheet names to export, please make a comment below and I'll amend or make a new answer.

CodePudding user response：

Here's a version that will overwrite existing workbooks with the same name without prompting the user

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"
            debug.print file_path
            With ActiveWorkbook
                .SaveAs Filename:=file_path, FileFormat:=xlOpenXMLWorkbook
                .Close SaveChanges:=False
            End With
        End If
    Next i

End Sub