Macro - Save As New File and Date

sarefeen

Board Regular
Joined
Feb 18, 2009
Messages
53
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,
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
Code:
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

Board Regular
Joined
Feb 18, 2009
Messages
53
:) 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

Legend
Joined
May 15, 2003
Messages
29,536
With the GetSaveAsFilename method Bob posted you can set the path:

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

HTH,

Who is Dennis?

EDIT: I'm an idiot
 
Last edited:

xld

Banned
Joined
Feb 8, 2003
Messages
5,378

ADVERTISEMENT

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

Who is Dennis?
 

sarefeen

Board Regular
Joined
Feb 18, 2009
Messages
53
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

Board Regular
Joined
Feb 18, 2009
Messages
53
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,483
Messages
5,596,405
Members
414,064
Latest member
Duncthegreat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top