Seperate excel files based on tab names by vba

Soumya197 · May 2nd 2020

Hi All,

I have excel file with multiple tabs with different tab names. I want to create new files with existing tabs from the excel file. For example say my present excel file has 6 tabs with tab name T1 to T6. I want to create new excel file with T2 and t3 and another with t4 t5 and t6. I will define this in T1. Is this possible by VBA coding.

Regards

Soumya

dangelor · May 3rd 2020

Possibly...

Code

Sub Files_from_Worksheets()

    ThisWorkbook.Worksheets(Array("T2", "T3")).Copy
    ThisWorkbook.Worksheets(Array("T4", "T5", "T6")).Copy
    
End Sub

Logit · May 3rd 2020

.

Without getting too fancy with coding .. here is one method :

Code

Option Explicit

Sub Tab2n3()

Worksheets(Array("TAB2", "TAB3")).Copy

    With ActiveWorkbook
         .SaveAs Filename:=Environ("USERPROFILE") & "\Desktop\Tab2n3.xlsx", FileFormat:=xlOpenXMLWorkbook
         .Close SaveChanges:=False
    End With

End Sub

Sub Tab4n5n6()

Worksheets(Array("TAB4", "TAB5", "TAB6")).Copy

    With ActiveWorkbook
         .SaveAs Filename:=Environ("USERPROFILE") & "\Desktop\Tab4n5n6.xlsx", FileFormat:=xlOpenXMLWorkbook
         .Close SaveChanges:=False
    End With

End Sub

[attach=1228065][/attach]

Display More

Soumya197 · May 3rd 2020

Thanks

Logit · May 3rd 2020

You are welcome.

Seperate excel files based on tab names by vba

Participate now!

Share

Tags