Add date and cell value to export code

[slayer1957]

Hi all,
I need to just add todays date and cell value "S2" to my current code below. I have small section to add but cannot get it right and cell value still also needs to be added. Can someone please help.

_" & _Format(Date, "ddmmmyyyy") & Cell.value "S2" & ".xlsm"

Sub EXPORT()


    Dim mySourceWB As Workbook
    Dim mySourceSheet As Worksheet
    Dim myDestWB As Workbook
    Dim myNewFileName As String
    
'   First capture current workbook and worksheet
    Set mySourceWB = ActiveWorkbook
    Set mySourceSheet = ActiveSheet


'   Build new file name based
    myNewFileName = mySourceWB.Path & "\" & mySourceSheet.Name & ".xlsx"


'   Add new workbook and save with name of sheet from other file
    Workbooks.Add
    ActiveWorkbook.SaveAs Filename:=myNewFileName
    Set myDestWB = ActiveWorkbook
    
'   Copy over sheet from previous file
    mySourceWB.Activate
    Cells.Copy
    myDestWB.Activate
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
'   Resave new workbook
    ActiveWorkbook.Save


End Sub

 

[Joe4]

Try:

 & Format(Date, "ddmmmyyyy") & Range("S2").Value & ".xlsm"

 

[slayer1957]

It is giving runt time error, extension cannot be used for selected file type, change file extension int he file name text box or select a different file type by chnaging the save as type and flags line" ActiveWorkbook.SaveAs Filename:=myNewFileName"

 

[Joe4]

Note that is you have an "xlsx" file type being saved as an "xlsm" file type, or vice versa, then you are changing file types and need to include that file format argument in your SaveAs command.
If you turn on the Macro Recorder and record yourself manually doing a SaveAs with your desired file type, you will see what that line of code needs to look like, i.e.

    ActiveWorkbook.SaveAs Filename:="file name here" _
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

 

[slayer1957]

Got it, thanks for the help!

myNewFileName = mySourceWB.Path & "\" & mySourceSheet.Name & "_" & Format(Date, "dd mmm yyyy") & "_" & Range("S2").Value & ".xlsx"

 

[Joe4]

You are welcome.
Glad I was able to help!