Copy sheet to new workbook

[DavidWaldo]

So, during past days I had several questions and recieved much help here. THANKS . I'm finally getting to the end of my project(automatization of giving contacts to sales reps every morning.) My excel file now downloads SQL data, filters it and spreads it among the sale reps. Each Sale rep has his own sheet. But I dont want them to acces the main file, I'll rather create new excel file for each sale rep. I have sheet names stored in an array, and I would like the new workbooks to have a same name, save the new workbook and close it.
I know it should be something like this

For i = lbound(Wnames) to Ubound(Wnames)
Set NewBook = Workbooks.Add
NewBook.title = wnames(i) 
NewBook.saveas = wnames(i) & ".xlxs"
Activeworkbooks.Sheets(Wnames(i)).move workbooks(Wnames(i))next i

but I cant get it working...I'l also would like to implement an safe feature, when one of the sheets doesnt exist - but i do not know how.
Thanks

[Paul_Hossler]

1. you has ".xlxs" and it should be ".xlsx"

2. This loops through the worksheets and creates a new workbook for each worksheet, save the WB as the sheet name

3. I added a automatic delete if the WB exists

4. I added a date stamp to the WB name (just because I like to) but you can delete that if you don't want

You'll have to integrate this, changing some things, into your over all project

Option Explicit
Sub Macro1()
    Dim ws As Worksheet
    Dim wb1 As Workbook, wb2 As Workbook
    Dim sName As String
    
    Application.ScreenUpdating = False
    
    Set wb1 = ThisWorkbook
    For Each ws In wb1.Worksheets
        ws.Copy
        Set wb2 = ActiveWorkbook
        sName = wb1.Path & Application.PathSeparator & ActiveSheet.Name & Format(Date, "-yyyy-mm-dd") & ".xlsx"
        
        On Error Resume Next
        Kill sName
        On Error GoTo 0
        
        wb2.SaveAs sName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        wb2.Close
    Next
    Application.ScreenUpdating = True
End Sub

[DavidWaldo]

Works like a charm, what if I would like to overwrite existing files?

[GTO]

Unless I misunderstand, Paul already included a Kill to delete any existing file.

[Paul_Hossler]

See #3 in Post #2