Save Each Worksheet as New Workbook -- File Name Issue

[marshak]

Hi All!

I'd like your help with the following. Thanks so much, in advance!!

Say I have three worksheets (for three separate individuals)
Each worksheet is named (Last Name, First Name)
I would like to save each worksheet as a new workbook, but instead of naming the workbook the same name as the worksheet, I'd like to name it: Last Name Evaluation Date
(instead of Last Name, First Name)

For Example:
I have 3 Worksheets named:
Doe, John
Russell, Mark
Carson, Kristi

I need 3 separate Workbooks named:
Doe Evaluation Data
Russell Evaluation Data
Carson Evaluation Data


Right now, here is the macro I am using (it creates the workbooks with the names of the worksheets):
___________________________________________________________________________________________
Sub Separate()
'
' Saves each sheet in a new workbook and assigns the Associate name as the filename.
'
Dim Sheet As Worksheet, SheetName$, MyFilePath$, N&
MyFilePath$ = ActiveWorkbook.Path & "" & _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)
With Application
.ScreenUpdating = False
.DisplayAlerts = False
' End With
On Error Resume Next '<< a folder exists
MkDir MyFilePath '<< create a folder
For N = 1 To Sheets.Count
Sheets(N).Activate
SheetName = ActiveSheet.Name
Cells.Copy
Workbooks.Add (xlWBATWorksheet)
With ActiveWorkbook
With .ActiveSheet
.Paste
.Name = SheetName
[A1].Select
End With
'save book in this folder
.SaveAs Filename:=MyFilePath _
& "" & SheetName & ".xlsx"
.Close SaveChanges:=True
End With
.CutCopyMode = False
Next
End With
Sheet1.Activate
End Sub

 

[Joe4]

Try changing your SheetName calculation from:

SheetName = ActiveSheet.Name

to:

SheetName = Left(ActiveSheet.Name, InStr(ActiveSheet.Name, ",") - 1) & [COLOR=#ff0000]Format(Date, "_yyyy-mm-dd")[/COLOR]

Note the date part of the calculation in red. You can change to any date format you desire.

 

[marshak]

Try changing your SheetName calculation from:

SheetName = ActiveSheet.Name

to:

SheetName = Left(ActiveSheet.Name, InStr(ActiveSheet.Name, ",") - 1) & [COLOR=#ff0000]Format(Date, "_yyyy-mm-dd")[/COLOR]

Note the date part of the calculation in red. You can change to any date format you desire.

Thanks so much for your answer. I will try it and let you know if it works.

If I don't need an actual date, just the phrase 'Evaluation Date' after the last name, what would the code be?

 

[Joe4]

Note that are two parts to my calculation:
- the last name
- the date
and they are concatenated together using the "&"

So all you need to do is remove the part after the "&" and replace with hard-coded text: " Evaluation Date", i.e.

SheetName = Left(ActiveSheet.Name, InStr(ActiveSheet.Name, ",") - 1) & [COLOR=#ff0000]" Evaluation Date"[/COLOR]

 

[marshak]

Thanks so much for replying and explaining that. Will try it out and let you know.