Macro to save file with active cell value

Microsoft Excel 2010
June 26, 2010 at 04:28:38
Specs: Windows XP
Dear All,

I am looking for a macro to save a file name with active cell value(where the cursor is placed) and with 3 date options.

Date options = a pop-up should come-up with buttons asking A B and C. If I click A = save the file with today's date. Click B = yesterday's date and Click C = day before yesterday's date.

In brief the flow of the macro should be:

Open the excel file>>>Place the cursor on the cell which should be the filename(the name may be anywhere in the sheet)>>> run the macro >>> A pop-up with with button A B and C>>> say click on B>>> file should be saved in a drive path with the file name and yesterday's date like _xyz_20100626.xls

Could anybody help me in building this macro for me, I would be greatful to them and this may change my life in the office I work.

Thanks a lot in advance for helping me.

See More: Macro to save file with active cell value

Report •

June 26, 2010 at 13:10:44

Here is a simple macro to allow you to save the active workbook with a combination of the name in the selected cell and a date.

The first option is to save with today's date - if that is rejected, you are asked to save with yesterday's date and if that is rejected, you are asked to enter a date.

Finally the filename and date are presented for final confirmation before saving.

The workbook is saved to the same directory as the last save and in the default format.

The macro could easily be changed to include a path/folder, rather than rely on what has recently been used.

The macro also tests that the selected cell isn't empty - so that you don't go through the steps only to find that there is no name.

I suggest that this macro, is stored in a module in an Excel Add-In so that it is always available, and the macro code does not end up in dozens of saved workbooks.
The macro is run from a button on the Quick Access Toolbar.

(I don't have Excel 2010, so I am going on Excel 2007 - hopefully the steps are the same - but I can't guarantee it).

Start with a new workbook. Save it as an Excel Add-In (*.xlam) - use the 'Other formats' option in 'Save as' and find the Excel Add-In option.
Name it e.g., "SaveAsName.xlam" and save.

From the Office button select Excel options (at the bottom) and select 'Add-Ins' then in the drop-down at the bottom select 'Excel Add-ins' and click 'Go'
In the dialog box click 'Browse' and your new add-in should be visible in the list - select it and click OK

SaveAsName should be checked in the Add-Ins dialog box then click OK

In Excel, click Alt + f11 (the Alt key and function key #11 clicked together), this opens up the Visual Basic window.
In the Project Explorer pane on the left find VbaProject(SaveAsName.xlam)
If the Project Explorer is not visible, select View from the VB menu and then click 'Project Explorer'
Right-click on SaveAsName.xlam and select 'Insert' and 'Module' (not Class module)
Double click the new module - typically 'Module1' under the Modules folder.
Enter this:

Option Explicit

Public Sub SaveAsName()
Dim strResp As String
Dim strDate As String

'test that selected cell is not empty
If ActiveCell = "" Then
    MsgBox "There is no text in the selected cell" & vbCrLf & _
            "Please enter a filename and try again"
    Exit Sub
End If

'ask for today
strResp = MsgBox("Save with Today's date", vbDefaultButton1 & vbYesNoCancel)
If strResp = vbCancel Then Exit Sub
If strResp = vbYes Then strDate = Format(Date, "yyyymmdd")
'if not today, ask for yesterday
If strResp <> vbYes Then
    strResp = MsgBox("Save with Yesterday's date", vbDefaultButton1 & vbYesNoCancel)
    If strResp = vbCancel Then Exit Sub
    If strResp = vbYes Then strDate = Format(Date - 1, "yyyymmdd")
End If
'if not yesterday, ask for date
If strResp <> vbYes Then
    strResp = InputBox("Enter a date")
    If IsDate(strResp) Then
        strDate = Format(strResp, "yyyymmdd")
        'no valid date entered so quit
        Exit Sub
    End If
End If
'display filename plus date - confirm
strResp = MsgBox("Confirm filename: " _
        & ActiveCell.Text & strDate, vbDefaultButton1 & vbOKCancel)
If strResp = vbCancel Then Exit Sub
ActiveWorkbook.SaveAs Filename:=ActiveCell.Text & strDate
End Sub

Click File - SaveAsName.xlam from the VB menu.
Use Alt + f11 to return to the main Excel window.

Click the drop-down at the end of the Quick Access toolbar
Select 'More Commands'
Select Customize on the left side and from the 'Choose commands from' drop-down select Macros.
Select SaveAsName
On the right side select an existing icon and click 'Add' which will place your new macro (icon) after the selected icon.
Click OK
The icon image cannot be edited directly - there are options - but beyond the scope of this response.

Open your file and select a cell with the required filename
Click the new icon and the new macro will run.

Note that the macro is stored in an Add-In file which will now be loaded automatically every time you start Excel.


Report •

October 17, 2010 at 16:40:49
How would i change the code if i wanted to use a specific cell say "B3" not a selected cell?

Report •

October 17, 2010 at 17:52:14
This should do it for you:

Open the code in the VBA editor.

Choose Edit...Replace

Find What: ActiveCell
Replace With: Range("B3")

Replace All (VBA should make 3 replacements)

Find What: selected cell
Replace With: B3

Replace All (VBA should make 2 replacements)

Save it and you should be good to go.

Report •

Related Solutions

October 21, 2010 at 14:47:14
could i add some code into this to make it also send worksheet to a specific email address?

Report •

Ask Question