Save Sheets in Separate File Debug Error

[Muhammad Shakeel Ishaq]

Hi All,

I am using below macro from several days to save each sheet as seprate workbook. Today i have received debug error, not know what is the reason, code is below while error screen shot is also attached:

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 Sub

Please help me.

 

[Abdul Matheen]

Hi Shakeel,

I tried with same code from my end and i didn't received any error. If possible could you send me the error message what you are receiving.

Regards
Abdul Matheen

 

[chirayu]

Part of your code is missing. You have used xWs but not defined it. You need the following code in your macro

Dim xWs As Worksheet

 

[Marc L]

Hi,

in previous line replace Sheets by Worksheets …
​

 

[Muhammad Shakeel Ishaq]

i have move sheets to new workbook and it work again. I guess there may an issue with the book.

Thank you for reply