Macro - Save As New File and Date

[sarefeen]

Hi all,

Here is what I want the macro to do:

Goto cell A1 and prompt me to insert a new date.

Then it should go to cell E1 and clear all contents from E1 to F100

Then the cursor should go back to A1 park and prompt me to save the file under a new name.

So that my old file is there and I start working with a new file.

Thanks guys for you usual prompt response.

Regards,

 

[xld]

Range("A1").Value = InputBox("Supply a new date")

Range("E1:F100").ClearContents

fName = Application.GetSaveAsFilename( _
    fileFilter:="Excel Files (*.xls), *.xls")
If fName <> "" Then Thisworkbook.SaveAs fName

 

[sarefeen]

Thank you so much. This worked. But I want files to be saved in one particular location for example C:\mydocuments\Daily Update. This macro is saving everytime in C:\.

Regards,

 

[Smitty]

With the GetSaveAsFilename method Bob posted you can set the path:

    '   Set to Specified Path\Folder
        ChDir "C:\Documents and Settings\All Users\Desktop\"

HTH,

Who is Dennis?

EDIT: I'm an idiot

 

[xld]

GetSaveAsFilename opens a file dialog, so you can navigate to whatever directory you wish.

Who is Dennis?

 

[sarefeen]

Hi all,

A delayed response with apologies. I am using xld's method and its working for me. Smitty thanks for your input too. Will try with your one soon. And let you know how it worked.

 

[sarefeen]

Hi Smitty/Xld,

Now that I got some time, wanted to update my macro, and wrote as follows:

Sub Test()
fName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
ChDir "C:\Documents and Settings\All Users\Desktop\"
If fName <> "" Then ThisWorkbook.SaveAs fName
End Sub

Still the file is not saved in my Desktop folder. Its going to last folder saved.

Please help