Save Sheet As File (with Sheet name)

[hansgrandia]

Hello,

I'm trying to copy every sheet in workbook as separate file (on X drive) with the name of the sheet with below standing code. It now creates a single file with the name %20&%20ws.name%20&%20 including all sheets. What goes wrong here?

Thank you, Hans Grandia (Netherlands)

Sub SaveAsFile()

Application.ScreenUpdating = False
Dim ws As Worksheet
'eerste sheet selecteren
Worksheets(1).Activate
For Each ws In Worksheets
ws.Activate
With ActiveSheet
     .SaveAs Filename:="X: & ws.name & .xlsx", FileFormat:=xlWorkbookDefault
     .Close SaveChanges:=False
End With
Next
End Sub

 

[AlphaFrog]

[color=darkblue]Sub[/color] SaveAsFile()
    [color=darkblue]Dim[/color] ws [color=darkblue]As[/color] Worksheet
    [color=green]'eerste sheet selecteren[/color]
    [color=green]'Worksheets(1).Activate[/color]
    Application.ScreenUpdating = [color=darkblue]False[/color]
    [color=darkblue]For[/color] [color=darkblue]Each[/color] ws [color=darkblue]In[/color] ThisWorkbook.Worksheets
        ws.Copy [color=green]'Copy the sheet as a seperate workbook[/color]
        ActiveWorkbook.SaveAs Filename:="X:\" & ws.Name & ".xlsx", FileFormat:=51
        ActiveWorkbook.Close SaveChanges:=[color=darkblue]False[/color]
    [color=darkblue]Next[/color]
    Application.ScreenUpdating = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[hansgrandia]

Thanks AlphaFrog!