Macro to Save File Based on Cell Contents

Microsoft Excel 2003 (full product)
May 24, 2010 at 01:20:38
Specs: Windows Vista
I am trying to write a macro for excel that will allow me to save as (Range("A1").Value) into a file that changes according to the value in cell "A2". The path will be constant.

The document I am making is a monthly log. Within the workbook I have sheets numbered 1 - 31 and another sheet called "Data Sheet". Each sheet from 1 - 31 has a date and the month and year can be easily changed in "Data Sheet" by changing the month in cell "A1" and the year in "A2". Right now the workbook is saved as "Log - Template". I want to be able to just click a button and have it save the whole workbook as <C:\Users\Tom\Desktop\FA\ (Here it would have the year that is listed in "A2") \Log - "A1" "A2".xlsm>. This way with in the 2010 folder there would be "Log - Jan 2010.xlsm", "Log - Feb 2010.xlsm", "Log - Mar 2010.xlsm", and so on. Then When I am ready to make "Log - Jan 2011" it would automatically make a 2011 folder and save the workbook in that folder.


See More: Macro to Save File Based on Cell Contents

Report •

#1
May 24, 2010 at 06:02:26
Your stats say you are using Excel 2003, but you used xlsm in your post.

I recorded then modified this in 2003, so some of the options may differ for 2007.

Just to be safe, you might want to record the Save As operation and then modify the code so it looks like my example.

You may need to include Sheet names, etc. The basic idea is to build the filename using the references to the Ranges. Don't forget the space.

Sub SaveMonthYear()
    ActiveWorkbook.SaveAs Filename:= _
     "C:\Users\Tom\Desktop\FA\" & Range("A2") & " " & Range("A1") & ".xlsm", _
     FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
     ReadOnlyRecommended:=False, CreateBackup:=False
End Sub


Report •

#2
May 24, 2010 at 10:39:00
This is wrong. This would make it look like this. <C:\Users\Tom\Desktop\FA\Log - Jan 2010.xlsm>. I want it to be <C:\Users\Tom\Desktop\FA\2010\Log - Jan 2010.xlsm>. I also want it to be able to check <C:\Users\Tom\Desktop\FA\> to see if it already has a "2010" folder. If so the save it in there. If not then to make a "2010" folder then save it in there. the code should look more like this:

Sub SaveMonthYear()
    File(Range("A2")).Search
    If Search = True Then
        ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\Tom\Desktop\FA\" & Range("A2") & _
        "\Log - " & Range("A1") & " " & Range("A2") & ".xlsm", _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    Else
        File(Range("A2")).Create
        ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\Tom\Desktop\FA\" & Range("A2") & _
        "\Log - " & Range("A1") & " " & Range("A2") & ".xlsm", _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    End If
End Sub

I know this has mistakes in it. Could you please help me fix them. Thank you.


Report •

#3
May 24, 2010 at 10:42:57
Oh and I am working on the file at home on Office 07 but it is for my work computer that uses Office 03. I'm hoping that it would work on both if I changed the .xlsm to .xls.

Report •
Related Solutions


Ask Question