Close and save workbook via macro without running 'beforesave' events

Question

I have a list of workbooks that follow the same template, and all have a macro 'beforesave' event, which basically creates a save-log in on of the sheets, listing the time and user id from whoever is saving the workbook.

So sometimes I need to change a formula in all workbooks, and since it takes around a minute to close and save each of them manually, I created a macro that opens all of them changes what I need changed, and closes saving them. That would save me some significant amount of time, as on total this process ends up taking around 30 mins of my time, and a lot of boredom.

Problem is: when the macro tries to close and save the workbook, the 'beforesave' event doesn't run properly and as a result the workbook doesn't save and close with the macro. For some reason, the event seems to be skipped in the macro...

To solve that I want to run this macro to make all the changes in all workbooks, skipping the beforesave event (if necessary) and actually saving and close them at the end.

Help please?

Tried running the macro line by line, and when it gets to the event part, it calls it, but for some reason it stays in the same sheet instead of going to the log sheet, and writes the log info in the wrong sheet as a result. In any case, running line by line won't obviously work for me as it's basically the same as doing the process myself, manually.

Sub DoStuff()
    Dim Row As Integer
    Dim Col As Integer
    Dim wbCopy As Workbook
    Dim wbPaste As Workbook
    Dim wbBP As Workbook

    For Col = 4 To 4
        ThisWorkbook.Activate
        Set wbBP = Workbooks.Open(Cells(1, Col), False)
        ThisWorkbook.Activate
        Set wbCopy = Workbooks.Open(Cells(2, Col), False, True)

        For Row = 3 To 19
            ThisWorkbook.Activate
            SetAttr Cells(Row, Col), vbNormal
            Set wbPaste = Workbooks.Open(Cells(Row, Col), False)
            wbCopy.Activate
                Sheets("Base").Activate
                Range("A7:EQ500").AutoFilter
            wbPaste.Activate
                Sheets("Base").Activate
                Range("A7:EQ500").AutoFilter
            wbCopy.Activate
                Sheets("Base").Activate
                Range("AL8:AS8").Copy
            wbPaste.Activate
                Sheets("Base").Activate
                Range("AL8:AS" & Cells(Rows.Count, 1).End(xlUp).Row).PasteSpecial xlPasteFormulas
                Application.CutCopyMode = False
            wbPaste.Close True
            ThisWorkbook.Activate
            SetAttr Cells(Row, Col), vbReadOnly
        Next Row
        wbCopy.Close False
        wbBP.Close False
    Next Col

End Sub

Running the macro to do the changes I need, and having all workbooks involved saved and closed properly.

Answer 1

Welcome to SO. The answer to the title is too simple if you are OK to execute your update job running any events in the wbPaste workbooks. Just simply adding Application.EnableEvents = False before the saving the file and make it true after the save completion.

Also based on the points commented by @Mathieu Guindon your post is confusing about your exact requirement and the inadvertent use of Activate I just restructured your code a little to avoid running any events during update.

Sub DoStuff()
    Dim Row As Integer
    Dim Col As Integer
    Dim wbCopy As Workbook
    Dim wbPaste As Workbook
    Dim wbBP As Workbook

‘Worksheet name “FileList” used for trial . May please change to yours  or use activesheet 
 With ThisWorkbook.Worksheets("FileList")    
       For Col = 4 To 4
       ‘Could not understand why wbBP opened, it is not used anywhere in the code 
       Set wbBP = Workbooks.Open(.Cells(1, Col), False)
        Set wbCopy = Workbooks.Open(.Cells(2, Col), False, True)

       ‘ This will disable any events including ‘BeforeSave’ events
        Application.EnableEvents = False
       ‘Disabling  ScreenUpdating  will increase efficiency if large files used
        Application.ScreenUpdating = False

          For Row = 3 To 19
            SetAttr .Cells(Row, Col), vbNormal           ‘ failed to understand use of SetAttr
            Set wbPaste = Workbooks.Open(.Cells(Row, Col), False)
            wbCopy.Sheets("Base").Range("A7:EQ500").AutoFilter
            wbPaste.Sheets("Base").Range("A7:EQ500").AutoFilter
            wbCopy.Sheets("Base").Range("AL8:AS8").Copy
            wbPaste.Sheets("Base").Range("AL8:AS" & Cells(Rows.Count , 1).End(xlUp).Row).PasteSpecial xlPasteFormulas
            Application.CutCopyMode = False
            wbPaste.Close True
            SetAttr .Cells(Row, Col), vbReadOnly
        Next Row
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        wbCopy.Close False
        wbBP.Close False
    Next Col
    End With
End Sub

If you want to run other events in the wbPaste workbooks and only intend to avoid running only BeforeSave events or a part of code in that event and you have access to modify the event codes, then you may resort to introduce a branch in the `BeforeSave’ events code by checking value of cell as per my comment. If apprehensive about cell value being accidentally modified/deleted by user, it is better to introduce check a ‘CustomDocumentProperties’

You may opt to add & set the custom Document Property from document panel of all the wbPaste workbooks. I would prefer to introduce the Custom Document Property BeforeSaveCheck‘ by onetime running the code

Sub testOnce()
    Dim Row As Integer
    Dim Col As Integer
    Dim wbPaste As Workbook
    Col = 4

    With ThisWorkbook.Worksheets("FileList")
        Application.EnableEvents = False
        'Application.ScreenUpdating = False
        For Row = 3 To 19
            SetAttr .Cells(Row, Col), vbNormal
            Set wbPaste = Workbooks.Open(.Cells(Row, Col), False)
            wbPaste.CustomDocumentProperties.Add Name:="BeforeSaveCheck", LinkToContent:=False, Type:=msoPropertyTypeBoolean, Value:=True
            wbPaste.Close True
            SetAttr .Cells(Row, Col), vbReadOnly
        Next Row
        Application.EnableEvents = True
        'Application.ScreenUpdating = True
    End With

End Sub

Now you may introduce the a simple branch in BeforeSave events of wbPaste workbooks like

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If ThisWorkbook.CustomDocumentProperties("BeforeSaveCheck") Then
‘’’’’’’’’’’’’
‘The code section you want to bypass while updating with macro
‘’’’’’’’’’’’
End If

End Sub

and setting the property to True at the open event

Private Sub Workbook_Open()
ThisWorkbook.CustomDocumentProperties("BeforeSaveCheck") = True
End Sub

Finally in sub dostuff Delete lines

   Application.EnableEvents = False
   Application.EnableEvents = True

And add line

ThisWorkbook.CustomDocumentProperties("BeforeSaveCheck") = False
wbPaste.Close True