asked on

save file using sheet name and text from a cell in another sheet

I need to save each sheet in my workbook [Except for the sheet named Rec]

The path is C:\Cpn\CA\Events\Red\2015 [which will change every year] \Mar 15 [which will change each month]

*I would like the macro to create a folder for year and month if there is no folder already there

the file name will be the sheet name and then the name in cell C38 from Sheet named Rec.

so file name would be like: Book3 - AB123456 - Red - 160315 [book3 being sheet name] [ - AB123456 - Red - 160315 being in cell C38 in sheet Rec]

so file path and name would be: C:\Cpn\CA\Events\Red\2015\Mar 15\Book3 - AB123456 - Red - 160315.xls

Thanks in advance

Saurabh Singh Teotia

Basis of rory code which he wrote here for you...

Save Macro

You can tweak this code like this...

Sub SaveMe()
Const csROOT_PATH As String = " C:\Cpn\CA\Events\Red\"

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.name & ActiveSheet.Range("C38").Value & ".xlsx" , FileFormat:=51

End Sub

Open in new window

Saurabh

Jagwarman

ASKER

I tried that myself but it does not do what I need.

I need to save each sheet in my workbook [Except for the sheet named Rec]

the other sheets [and there can be many of them] are the ones I need to save with the name of their sheet plus the text that is in cell C38 which is in the sheet named Rec.

Regards

ASKER CERTIFIED SOLUTION