How to change in the separated files the worksheet names automatically

[1234SAM]

Hello everyone,
I use the code above created from Mr. Sumit Bansal to seperate sheets automatically into new files, and that was wery hepful.
But my problem now is that in the new files created I need the worksheet to have different name than the file name.
For example if the sheet on the original file was called January, when I seperate them now, the new file is called January, and also the sheet is called January, the file name is ok, but I need the worksheet to have different name, on the new file.

Could you please help me with that?

'Code Created by Sumit Bansal from TrumpExcel.com
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

But my question is how to change in the separated files the worksheet names automatically?

For example If the seperated file Name is January, I dont want also the sheet to be called January I need to have another name for the sheet.
What could I add to this code, to change sheet name automatically to all new separated files?

If you could find some time and answer my questions that would be very helpful

Thanks in advance

 

[johnnyL]

We can't guess what you want the sheet name to be. What would you like the sheet name to be called, for example in the created January.xlsx workbook?

 

[1234SAM]

We can't guess what you want the sheet name to be. What would you like the sheet name to be called, for example in the created January.xlsx workbook?

I would like the sheet to have a person's name, for example Sam Collymore.

 

[johnnyL]

The following will change the name of the sheet prior to saving the new workbook. But unless you want all of the sheets in all of the created workbooks to have the same sheet name, you need to figure out how you want to correlate the new sheet name to the new workbook.

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

 

[1234SAM]

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

Thanks a lot

 

[johnnyL]

You are welcome @1234SAM.