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

Question

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?

@bigben yes! I selected each sheets (from which I want to separate) and save manually — Beatriz, Mar 24 '22 at 17:25
[This may help get you started](https://stackoverflow.com/questions/41872109/excel-macro-replace-formulas-and-save-sheet-as-own-file-without-changing-origin) — cybernetic.nomad, Mar 24 '22 at 17:27
while it may at first seem unrelated, [this should also prove useful](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — cybernetic.nomad, Mar 24 '22 at 17:38

BigBen · Answer 1 · 2022-03-24T17:54:10.147

2

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

edited Mar 24 '22 at 17:54

answered Mar 24 '22 at 17:48

BigBen

46,229
7
24
40

1

This is the code that makes for all the sheets? Sorry for the question, but I'm new to working with vba – Beatriz Mar 24 '22 at 20:57
1

That is correct. – BigBen Mar 24 '22 at 21:00
I'm testing the vba code that you provided me and the code only create/copy the first sheet and then gives an error like: "This extension can not be used with the selected file type. Change the file extension in the file name text box or select a different file type by changing the save as type". The error is on the line 15 – Beatriz Mar 25 '22 at 10:50
please check my question, I added information – Beatriz Mar 25 '22 at 11:10

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

1 Answers1