Summary Report for SplitEachWorksheet() VBA Function

[bftbeckett]

Hi all,

I am using the "Sub SplitEachWorksheet()" function in the VBA Module to get the file to split out into separate files for each sheet. However, I need a Summary report of the files created. Is this possible?

Preferably a summary as follows:
Worksheet name | File Name Created

Current Code:

Sub SplitEachWorksheet()
Dim FPath, FileExtStr, DateString, xFile As String
Dim FileFormatNum As Long
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 & ".csv"
    Application.ActiveWorkbook.Close False
Next
 
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[bftbeckett]

Adding more details.

My thought is that if I can save the worksheet names and filenames in an array and loop through it at the end to create a file with the worksheet name and the filename created.

I understand that the way this is written is having just the file name = the worksheet name. However, there is other criteria that is written that is not included due to proprietary info.