VBA Dynamic filename

[purpleozzie]

Hi guys,

I use this piece of VBA Code to save a file as a PDF on to a users desktop.. I would like to change it so it changes the filename depnding on a cell reference and todays date...

Sub create()
'
' Macro1 Macro
'

'
Sheets("QUOTE SHEET").Select
Sheets("QUOTE SHEET").Copy
Dim oSHELL As Object, sDesktopPath As String
Set oSHELL = CreateObject("WScript.Shell")
sDesktopPath = oSHELL.SpecialFolders("Desktop")


ChDir sDesktopPath
ActiveWorkbook.SaveAs Filename:= _
sDesktopPath & "\Customercopy.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
sDesktopPath & "\Customercopy.pdf", Quality:=xlQualityStandard _
, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
ActiveWindow.Close
End Sub

the cell reference is from a different worksheet called 'June 2015' and I want the date format in dd-mm-yy..

Next month the worksheet will be renamed July 2015 and I also want it to be able to update to still work..

Cheers,

 

[yky]

Change the filename part (till comma) to:

ActiveWorkbook.SaveAs Filename:= _
sDesktopPath & Activeworkbook.sheets("June 2015").cells(3,4).value & TEXT(NOW(),"mmm-yyyy"),

Need to change Cells(3,4) to the cell that contains the file name.

 

[Joe4]

To dynamically identify the sheet name every month (without having to hard code it), you can use this block of code to dynamically build your file name:

    Dim mySheetName As String
    Dim myFileName As String
'   Figure out sheet name from current date
    mySheetName = Format(Date, "mmmm yyyy")
'   Build file name by looking up cell A1 on sheet name and add date
    myFileName = Sheets(mySheetName).Range("A1") & Format(Date, "dd-mm-yy")

You didn't say which cell you want to pull the name from, so I assumed "A1". Just replace "A1" with whatever cell you want to return it from.

So the just use "myFileName in place of your hard-coded file name, and add the extension.

 

[purpleozzie]

Thanks Joe worked a charm.......