Save all worksheets to separate files in the current folder (the one this workbook is in) using sheet names as file names.

[Anne Troy]

I have tried using many different macros out there, and none of them work, or they stopped working for no apparent reason. I have 10 worksheets, and one macro saved only 3 worksheets as sheet1... sheet2 instead of the names I assigned. I opened them, and they were all empty. Some just did nothing. One saved as sheet1, sheet2 in my XLSTART folder, when the path was clearly defined.

I'd love the simplest code, no dialogs, no error handling... it's just for me to use. Anybody? Please?

Let me know if you have any questions.

 

[Anne Troy]

Here's the code that only gives me the first 3 sheets.

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

 

[smozgur]

Hey, Queen!

The current code uses ThisWorkbook, it is supposed to be Activeworkbook. Please try the following.

Sub SplitEachWorksheet()
Dim FPath As String
Dim ws as Worksheet
    FPath = Application.ActiveWorkbook.Path
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each ws In ActiveWorkbook.Sheets
        ws.Copy
        ActiveWorkbook.SaveAs FileName:=FPath & "\" & ws.Name & ".xlsx"
        ActiveWorkbook.Close False
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

(Isn't this just like 20+ years ago? It feels like yesterday!)

 

[Anne Troy]

Hey, Queen!

The current code uses ThisWorkbook, it is supposed to be Activeworkbook. Please try the following.

Sub SplitEachWorksheet()
Dim FPath As String
Dim ws as Worksheet
    FPath = Application.ActiveWorkbook.Path
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each ws In ActiveWorkbook.Sheets
        ws.Copy
        ActiveWorkbook.SaveAs FileName:=FPath & "\" & ws.Name & ".xlsx"
        ActiveWorkbook.Close False
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

(Isn't this just like 20+ years ago? It feels like yesterday!)

The like button has no heart!! Trying it now...

 

[Anne Troy]

Of course it works beautifully!! Thank you PumpKing!

 

[smozgur]

Thank you PumpKing!

LOL!

By the way, there are new features on the forum now. Please click on the checkmark right next to the post that answered your question to mark it as solution. This way, the other visitors looking for a similar title will know this question has a solution.

The like button has no heart!! Trying it now...

There is also a giant heart right above it!! LOL!

Nice to see you, Anne. Always.

 

[Anne Troy]

Marked Solved!

Are you busy? I need a macro that takes THIS worksheet, and separates into a worksheet for 10 zones (or each unique zone) in the column H. I have no code for that and only keep finding making sheets from cell values.

 

[smozgur]

Marked Solved!

Thanks!

I was just about to get away from the computer when I saw your question, but post the question as a new thread. There are still many members online who would respond before I get back.