1004 RUN TIME ERROR

[venkat1212]

Hi Friends,
I want to make separate excel files for this.


I used the VBA code:

Sub Splitbook()
'Updateby20140612
Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
xWs.Copy
Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End

And the result is:

After Debug it is showing ike this,

how to resolve....


▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !

​

 

[Chihiro]

Welcome to the Forum.

Add debug.print line in code before SaveAs line to check string. Make sure that file name (i.e. sheet name) does not contain any illegal character.

Debug.Print xPath & "\" & ws.Name & ".xlsx"

While file format is optional, it's safer to add it as you are specifying to file extension ".xlsx" (i.e. FileFormat:=51 or xlOpenXMLWorkbook)

Other than that, code itself is fine.

Also as per error message, make sure that you have appropriate permission on the folder that you are saving new file on.