saving workbook with a macro


Well-known Member
Aug 29, 2009
Office Version
  1. 2007
I want to save my workbook with a macro i can put on a button.

I'm writing the workbook in 2007 but it will be used by several novice users who have 2010 and i want to make saving easy and consistent for them.

I am constructing the full path and filename on one of the sheets thus:

A1: full path to folder on the users' network
A2: generic part of filename e.g. 'weekly diary'
A3: dynamic part of filename (the date for monday of the current week)
A4: file extension ( will be .xlsm to preserve macros
A5: full path and filename from concatenating the above e.g. 'C:\users\john\diaries/weeklydiary220914.xlsm'

this will cause diaries to auto-archive for previous weeks by using a new filename each week.

Please can anyone suggest macro code to do this? I assume it goes into a workbook module so it can be run from a button on any sheet?

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Try with an ActiveX button on the sheet containing the path etc

Private Sub CommandButton1_Click()
ActiveWorkbook.SaveAs Filename:=Range("A5").Value, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub
Upvote 0
my end users are on a corporate network and i think they'll have problems with active x.

Is there a way I can record a macro and then modify it with your code?

This is the code that came from my recorded macro:

Sub save()'
' save Macro

    ChDir "C:\Users\john\Dropbox"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\john\Dropbox\SESDTS jc 190914V1.xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

i used the save-as dialogue and accepted all the setting as offered.
Upvote 0

Rich (BB code):
Sub asave()
ActiveWorkbook.SaveAs Filename:=Sheets("Sheet1").Range("A5").Value, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub

Change Sheet1 to suit.
Upvote 0
perfect - thanks!
Upvote 0
Possibly another option...

Sub SaveToDiary()
    Dim sDay As String, sDay2 As String, sDay3 As String

    sDay = Date - Weekday(Date, vbMonday) + 1
    sDay2 = Replace(sDay, "/", "")
    sDay3 = Left(sDay2, Len(sDay2) - 4) & "14"

    ThisWorkbook.SaveAs Filename:="C:\Users\" & Environ$("Username") & _
                                  "\Diaries\" & "weeklydiary" & sDay3, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub

Please note that they do need a folder called Diaries in their username folder
Upvote 0
is there some way i can avoid or suppress the 'file already exists' warning message?
Upvote 0
Not sensible to suppress it if you are naming the file with the same name, which you are if you are running the code I posted in the same week as it will always name it with that Mondays date as you stated unless you want to over write the file (which you haven't said you wanted yet)
Last edited:
Upvote 0
Sorry, I should have said that yes I do want them overwritten. A dozen people will be changing it several times a day - maybe even at the same time - so it will need to be over written.
Upvote 0
Try putting Application.DisplayAlerts = False before the line and Application.DisplayAlerts = True before the End Sub line.
There is another way if it doesn't work for you but will have to wait until I am on my laptop rather than a phone.
Upvote 0

Forum statistics

Latest member

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
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 "".
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