VBA to map workbook sheets to folders with the same name as the sheet

[JasonCorbett]

Hello!

I am currently using a Sub that splits each worksheet from a single active workbook into the same folder that the single active workbook is located.

I would instead like to map the sheets to a folder with the same name as the sheet itself. Sheet ABC to Folder ABC, so on and so forth.

Here is the current code:

'Code Created by Sumit Bansal
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

------------

I have tried to update the .SaveAs path to no avail and keep getting error messages. Any help would be greatly appreciated.

Thank you!

 

[johnnyL]

If the folder name doesn't already exist you must create it with MkDir.

 

[JasonCorbett]

Thanks for the quick reply Johnny!

The folders do exist, but the code that I’m using (credit: Sunny Bansil) is saving the files to the same folder as the active workbook.

I have created sub folders as shown on my screenshot, and want to save Sheet ABC to Folder ABC after the macro splits them from the original workbook.

Hope that makes sense? Thank you

 

[Micron]

maybe
Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & "\" & ws.Name & ".xlsx"

 

[JasonCorbett]

Thank you Micron. I will check that output when I return this evening and let you know how it goes.

Happy Saturday,
Jay

 

[JasonCorbett]

maybe
Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & "\" & ws.Name & ".xlsx"

That worked! Thank you VERY VERY much!

 

[Micron]

You're welcome.