We help IT Professionals succeed at work.

vba to save file using reference from cell in sheet

Jagwarman asked
could an expert help me out with the code to save a file using the data in cell C36 to create the name of the file. I also need the vba to save the file in a folder where year will change and month will change, so:

file name is taken from Cell C36 [i.e.Part Red Rec 1234]

Path is:  G:\Cpn\CA\Events\Partial\current\current month\file name

Year is current year [If folder doesn't exist, create it]

Month is Current Month [i.e. Mar 15] ... [If folder doesn't exist, create it]

Result would be Save file as:

G:\Cpn\CA\Events\Partial\2015\Mar 15\Part Red Rec 12345

Watch Question

Most Valuable Expert 2011
Top Expert 2011
For example:

Sub SaveMe()
Const csROOT_PATH As String = "G:\Cpn\CA\Events\Partial\"

If Dir(csROOT_PATH & Year(Date), vbDirectory) = vbNullString Then
    MkDir csROOT_PATH & Year(Date)
End If
If Dir(csROOT_PATH & Year(Date) & "\" & Format(Date, "mmm yy"), vbDirectory) = vbNullString Then
    MkDir csROOT_PATH & Year(Date) & "\" & Format(Date, "mmm yy")
End If
ActiveWorkbook.SaveAs Filename:=csROOT_PATH & Year(Date) & "\" & Format(Date, "mmm yy") & "\" & ActiveSheet.Range("C36").Value & ".xlsm", FileFormat:=52

End Sub

Open in new window


brilliant thanks

Explore More ContentExplore courses, solutions, and other research materials related to this topic.