Save excel file as separate file name

[webmax]

Hi
I have a excel sheet with two sheets and having 3 data inside that (Emp code, Emp Name and Location)
Sheet 1 contains Format
Sheet 2 Contains Data File it contains three data (Emp code, Name and Location)
Now i want copy 1st employee code, name and location and paste in the Format sheet and save as excel file format with Emp code and emp Name.
Again the 2nd Employee code, name and location and paste in the format sheet and save as excel file format with emp code and emp name
like 3rd employee so on.
For example Emp Id M0001 , Emp Name John and Location UK like wise i want to make three excel workbook.
Attaching my sample file.
Thanks

 

[Keetoowah]

Hi webmax
try this code, adapt the path if needed

Sub test()
    
    Dim WksFormat As Worksheet
    Dim WksData As Worksheet
    Dim cel As Range
    Dim MyPath As String, MyFile As String
    
    Set WksFormat = ThisWorkbook.Sheets("Format")
    Set WksData = ThisWorkbook.Sheets("Data")
    
    MyPath = ThisWorkbook.Path & "\"
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    
    WksFormat.Range("A2:C2").ClearContents
    WksData.Activate
    
    For Each cel In WksData.Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        WksFormat.Range("A2") = cel.Value
        WksFormat.Range("B2") = cel.Offset(0, 1).Value
        WksFormat.Range("C2") = cel.Offset(0, 2).Value
        
        MyFile = WksFormat.Range("A2").Value & " " & WksFormat.Range("B2").Value & ".xlsx"
        
        With WksFormat
            WksFormat.Copy
            Application.ActiveWorkbook.SaveAs MyPath & MyFile
            Application.ActiveWorkbook.Close False
        End With
        
    Next cel
    
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
    
End Sub