'Save SPREADSHEETS in multiple excel files with the name of the main file

I have an Excel file with 4 auxaliary sheets + 7 sheets with tables. I would like to copy and separate each sheet (of the 7 sheets) into multiple excel's, so that each excel file has only 1 table. These sheets starts with "Lista", as for example "Lista_AA", "Lista_BB"...

After I would like to save these sheets with same name they had in the main excel. I don't have code because I try with with macro recorder and didn't function.I have already looked for several videos and questions on this site and they are a little different from what I want

I have this code for create these sheets in pdf:

Sub excels()

Application.ScreenUpdating = False

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


With Sheets(i)
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & nome_arquivo & ".pdf"

End With

Next i

Application.ScreenUpdating = True


End Sub

Is it possible to adapt for Excel files for same sheets?

Solution 1:^[1]

Use a loop:

Const filepath As String = "https://agits-my.sharepoint.com/personal/Documents/Desktop/Cantina/"

Sub macro()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name Like "Lista_*" Then
            SaveCopy ws:=ws
        End If
    Next
End Sub

Private Sub SaveCopy(ByVal ws As Worksheet)
   ws.Copy
   Dim wb As Workbook
   Set wb = ActiveWorkbook

   wb.SaveAs FileName:=filepath & ws.Name & ".xlsx", _
             FileFormat:=xlOpenXMLWorkbook, _
             CreateBackup:=False
   wb.Close SaveChanges:=False
End Sub

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1

'Save SPREADSHEETS in multiple excel files with the name of the main file

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]