Run Macro when saving the workbook

**papasioux** · 05-08-2021, 02:02 PM

Hi guys.

I Have 2 files, A and B. I want a Macro that runs from B every time I save the file. The Macro will open A and afterwards will close it again. So far the Macro looks as follows:

Sub OpenA()

Workbooks.Open Filename:="E:\CV3\A.xlsm"
Dim FileToClose As String
FileToClose = "E:\CV3\A.xlsm"
Workbooks(Dir(FileToClose)).Close
End Sub

I have added an event BeforeSave on ThisWorkbook but is not executing the macro

What I don´t know how to add to this code is:
How to execute the Macro once I save "B" considering is not working
Once "A" opens, there are references that need to be updated. How can I include that in the Code?
"A" needs to save before close....I don´t know how to do that either....

sorry if those are very simple questions but I´m not a pro on this XD.

Thanks in advance!

**Sequoyah** · 05-08-2021, 04:40 PM

Hi papasioux,
it's unclear what you mean by updating references on file A, you may need to adapt the code where indicated.
Try this code, in ThisWorkbook Module (not standard module) of file B:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    Workbooks.Open Filename:="E:\CV3\A.xlsm"
    
    With ActiveWorkbook
        
        .Sheets(1).Calculate        '====> adapt the code according to the operation you need to perform on file A
        
    End With
    
    Dim FileToClose As String
    
    FileToClose = "E:\CV3\A.xlsm"
    
    Workbooks(Dir(FileToClose)).Close SaveChanges:=True
    
End Sub

**papasioux** · 05-09-2021, 01:54 AM

Thanks Sequoyah! I meant updating references to A by external links to other books. I solved that by deactivating the confirmation in advanced options.

**jolivanes** · 05-08-2021, 05:42 PM

What needs to happen with the file you want opened?
Put in the "ThisWorkbook" module.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wb2 As Workbook
Set wb2 = Workbooks.Open("E:\CV3\A.xlsm")
    'Do what needs to be done in wb2 here
wb2.Close True    '<---- save and close wb2
End Sub

**papasioux** · 05-09-2021, 01:51 AM

Thanks Jolivanes! it worked.

I don´t need to do something specific on A once is open. I just need A to open for a while because I have information from B in A. I needed both files to be open so that the information is updated from B to A.
Cheers!

**jolivanes** · 05-09-2021, 02:20 AM

Thanks for letting us know and good luck
Have an Obstler on your health

Run Macro when saving the workbook

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Run Macro when saving the workbook

Re: Run Macro when saving the workbook

Re: Run Macro when saving the workbook

Re: Run Macro when saving the workbook

Re: Run Macro when saving the workbook

Re: Run Macro when saving the workbook

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Block from workbook saving from personal workbook macro

[SOLVED] Saving a new workbook with macro

[SOLVED] Macro to Saving Workbook Sheets as Separate Macro-Enabled Workbooks

[SOLVED] Saving workbook as 2003 xls then reopen macro enabled workbook

Renaming Active.Workbook and saving it without saving Macro

Macro for saving workbook

Disable one macro when saving a macro-enabled workbook while other macros continue.

Bookmarks

Bookmarks

Posting Permissions