save as copy the active work book based on active sheet name add some other contains

[nkashyap3]

Hi am not able the add some contains if i save as the active workbook .
I want to save as the excel file based on tab name with add additional contains in mid

if my active tab name is ML51HJ0 MTD Aug(tab name not fixed it will change)

my query is save as the file name ML51HJ0_AcntAnalysis MTD Aug Actuals

I have many file which i need same thing. (_AcntAnalysis) and (Actuals) will add in all excel file.

Sub save_003()
MName = ActiveSheet.Name & "Actuals" & ".xlsx"
MDir = ActiveWorkbook.path
ActiveWorkbook.SaveCopyAs Filename:=MDir & "" & MName
End Sub

please help to rectify this .

 

[DanteAmor]

Try this:

Sub save_003()
  Dim MName As String, MDir As String
  MName = WorksheetFunction.Substitute(ActiveSheet.Name, " ", "_AcntAnalysis ", 1) & " Actuals" & ".xlsx"
  MDir = ActiveWorkbook.Path
  ActiveWorkbook.SaveCopyAs Filename:=MDir & "\" & MName
End Sub

 

[nkashyap3]

Thanks a lot Dante, great work and code if perfectly working according to my requirement. you always accurate answer

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.