Hi
Welcome to ExcelFox !!
Use code tag while posting macroThis time I have added for you.
try
Code:Option Explicit ' Move and make a copy of each tab from the master file and save as a new workbook. Name it tab name - master file name Sub copy_save() Dim pName As String Dim wbName As String Dim shtName As String Dim TabName As String Dim NewName As String Dim i As Long Dim MyShts MyShts = Array("FR", "GM", "CH") pName = ThisWorkbook.Path ' the path of the currently active file, the master file wbName = ThisWorkbook.Name ' the file name of the currently active master file For i = LBound(MyShts) To UBound(MyShts) shtName = ThisWorkbook.Worksheets(MyShts(i)).Name ' the name of the currently selected worksheet, the master file TabName = pName & "\" & shtName & " - " & wbName 'Name the new workbook as: Tab name - master file name ThisWorkbook.Worksheets(shtName).Copy ActiveWorkbook.SaveAs Filename:=Left(TabName, InStrRev(TabName, ".") - 1), FileFormat:=51 ActiveWorkbook.Close 0 NewName = Left(TabName, InStrRev(TabName, ".")) & "xls" Name Left(TabName, InStrRev(TabName, ".")) & "xlsx" As NewName Next i End Sub




This time I have added for you.
Reply With Quote

Bookmarks