Macro to save then clear form data

dichronic

New Member
Joined
Jun 25, 2009
Messages
1
I have an excel form, I want my users to be able to open the form, fill in appropriate data, then click the save button. When the button is clicked, the data in the form should be saved to a new file and then the original form should be reset (clear all values) for a new set of data entry. When the data is saved, I want the new excel sheet to be saved to a location specified within the macro so the user doesn't have any options and I want the title of the new file to be the value of one of the cells.

I'm new to macros and would appreciate any help this board can offer. Thanks...
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Diablo II

Well-known Member
Joined
Sep 28, 2008
Messages
538
from what i can see, that what you wont can be done, if you have a workbook that you open and you save it with a new name the other workbook you open would not have changed so no need to clear the
Values.

here is same code to use Cell A1 for the name of the workbook

Code:
Sub Saveworkbook()
Application.DisplayAlerts = False
Dim dName$, vName$
    dName = Range("A1") ' here you can set the cell with the name you wont 
    vName = ActiveWorkbook.FullName
    ActiveWorkbook.SaveAs "C:\AAA\" & dName ' here set the path, i used c:\aaa\ for a path 
'here is just a note you may wont to allso set a date and time with the name, only 1 workbook with that name can be saved in that foulder or it will over right the workbook each time. 
    ActiveWorkbook.SaveAs vName
    ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub

here is the code with the date and time with space so it dont look like a buch on numbers put there.

Code:
 ActiveWorkbook.SaveAs "C:\AAA\" & dName & "      " & Format(Date, "MM.DD.YY") & "     " & Format(Time, "hhmm")
 

Vijetha Vastrad

New Member
Joined
Feb 13, 2014
Messages
2
Hi , I tried your code for saving the entire file in a Location with the following code.
But its not working as expected. When i Click the Button the entire Excel goes blank , including Ms Visual Basic screen, only tool bars remain.
I have to forcefully exit and reopen the file again
Please see my code and help me this.

Private Sub CommandButton1_Click()
If Range("F6").Value = "" Then
MsgBox ("Please fill in cell F6")
ElseIf Range("F13").Value = "" Then
MsgBox ("Please fill in cell F13")
ElseIf Range("F15").Value = "" Then
MsgBox ("Please fill in cell F15")
ElseIf Range("F17").Value = "" Then
MsgBox ("Please fill in cell F17")
Else
Application.DisplayAlerts = False
Dim dName$, vName$
dName = Range("F6")
vName = ActiveWorkbook.FullName
ActiveWorkbook.SaveAs "C:\" & dName & " " & Format(Date, "MM.DD.YY") & " " & Format(Time, "hhmm")
ActiveWorkbook.SaveAs vName
ActiveWorkbook.Close
Application.DisplayAlerts = True
End If
End Sub
 

numba

New Member
Joined
Aug 26, 2016
Messages
6
Hi Guys,
Just wondering if you got this to work. I have a form that populates three different sheets & want to do the same thing with the form. Save the whole workbook as a job name & clear the form for next use.
M
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,893
Messages
5,834,272
Members
430,273
Latest member
Windrunner

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