Macro to Save workbook by saving with month and year in Cell E6

[howard]

I have the month end Month and Year in Cell E6

I have the file name below

David James Comm.Oct 2021 New deals Ver 1.1.xlsm

I would like a macro to save the file by first removing the month and year after the . and before "New" and to include the month and year in Cell E6 after the .

If E6 contains Nov 2021 then the new file should save as David James Comm.Nov 2021 New deals Ver 1.1.xlsm


Your assistance is most appreciated

 

[howard]

Many thanks for the help. The Macro now gives me a run time error

I tried the formula below in F6 and get #Value!

 =GetDateFromString(E1)

i have uploaded my file on Drop Box to make it easier when you look at what I am doing wrong

kindly check & advise

Login - Dropbox

Login to Dropbox. Bring your photos, docs, and videos anywhere and keep your files safe.

www.dropbox.com

 

[Zot]

Never had a Dropbox account. Usually when people share I don;t have to login or do anything. Just download. Not sure how in this case since it is asking for login/password.

I'm confused. The Function is to be called by Sub Test, not as formula itself through worksheet. I thought you want to put the macro in workbook
David James Comm.Oct 2021 New deals Ver 1.1.xlsm

Then you will have Nov 2021 (string) in E6. You run the Sub Test. Then it will SaveAs
David James Comm.Nov 2021 New deals Ver 1.1.xlsm

The original file will still be there.

 

[howard]

Se if this works

Copy Run Numbers templates from Network.xlsm

Shared with Dropbox

www.dropbox.com

 

[Zot]

Se if this works

Copy Run Numbers templates from Network.xlsm

Shared with Dropbox

www.dropbox.com

Yes I can download this but this is not the filename you were talking about, right?

 

[howard]

My apologies

See correct ink to File

David James Comm.Oct 2021 New deals Ver 1.1.xlsm

Shared with Dropbox

www.dropbox.com

 

[Zot]

Your date is not in text but Date format. So, I used to capture as displayed on sheet. The Date is located at E1, not E6 as initially stated. I also have double declared parameter previously causing an error. Now your Date is in format Oct-2021. So, I modified pattern to capture both Oct 2021 and Oct-2021.

This should work

Sub Test()

Dim oldFName As String, newFName As String
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ActiveWorkbook
Set ws = wb.Sheets("Comm Sheet")  ' Modify if necessary

Application.ScreenUpdating = False

With ActiveWorkbook
    oldFName = .FullName
    newFName = GetDateFromString(ws, oldFName)
    .SaveAs Filename:=newFName
End With

End Sub

Public Function GetDateFromString(ws As Worksheet, inputString As String) As String

Dim RegEx As Object

Set RegEx = CreateObject("VBScript.RegExp")
GetDateFromString = ""

With RegEx
    .Global = True
    .Pattern = "\.[A-Z][a-z]{2}[\s-]\d{4}"
    GetDateFromString = .Replace(inputString, "." & ws.Range("E1").Text)
End With

End Function

 

[howard]

Many thanks Zot. This works 100%

 

[Zot]

Many thanks Zot. This works 100%

Thanks for update.