Copy and rename file from list

[Wil Moosa]

I have one file; Mother.xls

Mother.xls has on sheet(6) a list with names of all the employees. I want to create as many copies of Mother.xls as found on the list on sheet(6) each file carrying the name as mentioned on the list.

The following code does the trick for me:

 

[Kenneth Hobson]

Sub CopyToEachName()
    Dim cell As Range
     ThisWorkbook.Save
    For Each cell In Worksheets("Sheet6").Range("A2", _
        Worksheets("Sheet6").Range("A2").End(xlDown))
        ThisWorkbook.SaveCopyAs _
            ThisWorkbook.Path & Application.PathSeparator & cell.Value & ".xls"
    Next cell
End Sub

 

[Wil Moosa]

Kenneth

Sorry, something went wrong last night. I posted my question twice and unfortunately on this one only half. Please have a look here http://www.mrexcel.com/forum/showthread.php?p=1807636#post1807636

 

[Kenneth Hobson]

I already solved your problem. This method is better than SaveAs. It replaces the files if they exist already.

 

[Wil Moosa]

I do see the potential of your code... and it did work. Any practical suggestion for deleting sheet6 in the copies?

 

[WinteE]

A For .. Next loop in a personal macro keeps active after you save the file with another name.

First store the name of the list in a variable before you delete sheet(6). SaveAs xxx.xls, close xxx.xls and re-open the original file.

 

[Kenneth Hobson]

Sub Sheet6CopyToEachName()
    Dim sWB0 As String
    Dim a, v
    
    ThisWorkbook.Save
    sWB0 = ThisWorkbook.FullName
     
     a = Worksheets("Sheet6").Range("A2", _
        Worksheets("Sheet6").Range("A2").End(xlDown))
        
    ThisWorkbook.Save
    Application.DisplayAlerts = False
    Worksheets("Sheet6").Delete
    Application.DisplayAlerts = True
    
    For Each v In a
        ThisWorkbook.SaveCopyAs _
            ThisWorkbook.Path & Application.PathSeparator & v & ".xls"
    Next v
    
    Application.DisplayAlerts = False
    Workbooks.Open sWB0
    Application.DisplayAlerts = True
End Sub

 

[Wil Moosa]

Kenneth, it does the trick. Thanks.

WintE thanks too...