Run VBA Code On Save

[sathish2009]

Hi,

My current macro code is

     Sub Autosave()     
     Dim Wkb As Workbook     Dim Wkbnew As Workbook
    Dim FilenamePath As String
    Set Wkb = ActiveWorkbook
    FilenamePath = Wkb.FullName
    Wkb.SaveAs ThisWorkbook.Path & ("\MyExcelData - " & Format(Now(), "DD-MMM-YYYY_ hh_mm AMPM") & ".xlsm")
    Kill FilenamePath
End Sub

And i would like to execute the vba code automatically on saving the file.

Thanks

 

[tygrrboi]

Put this code into the code for ThisWorkbook (in your project window)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
     Dim Wkb As Workbook     Dim Wkbnew As Workbook
    Dim FilenamePath As String
    Set Wkb = ActiveWorkbook
    FilenamePath = Wkb.FullName
    Wkb.SaveAs ThisWorkbook.Path & ("\MyExcelData - " & Format(Now(), "DD-MMM-YYYY_ hh_mm AMPM") & ".xlsm")
    Kill FilenamePath
End Sub

 

[sathish2009]

Hi

i updated the code in my current module but it doesn't work for me

 

[tygrrboi]

This is not for your module. It is for the workbook code. In your project window you will see your module. You will also see all your sheets above that and one called "This Workbook"

The code I mentioned should go into ThisWorkbook, not a new module or any existing one.



(Also looking at the above code, if you haven't done so already, please put those two Dim statements on separate lines. Its hard to tell on this message board if that is actually how it looks in your VBA or if it is just the HTML of this message board being cranky...)