Thread: Run macro with more than 1 workbook open

Hi All,
I currently have a macro set up to run once a workbook is saved/ closed. This works fine unless tehre is more than one workbook open. When I try it with more than 1 different workbook open I get a run error.

My code begins:

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

and then performs the macro. I don't really want to specify the filename as this is liable to change. Is there anyway of adding a "this workbook" command to the text string, and if so where would it go?

Thanks

Hi,

I hope you are using ThisWorkbook everywhere in the code.

If you post the entire code, I can try to sort it out. Please give it in Code Tags so that it is easy to read.

--Karan--

Hi Karan,

please see the below code

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


Sheets("Main").Select
ActiveSheet.Unprotect ("password")
Dim lRow As Long, lCounter As Long
lRow = Cells(Rows.Count, 1).End(xlUp).Row
For lCounter = 2 To lRow
    If Cells(lCounter, 17) = "X" Then
        With Cells(lCounter, 13).Resize(1, 3)
        .Value = .Value
    End With
    End If
Next lCounter
Range("A5:O310").Select
    Selection.Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True, AllowFiltering:=True, Password:="password"
Range("B5").Select
Sheets("Open").Select


End Sub

Thanks

Use this code:

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

ThisWorkbook.Sheets("Main").Select
ThisWorkbook.ActiveSheet.Unprotect ("password")
Dim lRow As Long, lCounter As Long
lRow = Cells(Rows.Count, 1).End(xlUp).Row
For lCounter = 2 To lRow
    If ThisWorkbook.ActiveSheet.Cells(lCounter, 17) = "X" Then
        With ThisWorkbook.ActiveSheet.Cells(lCounter, 13).Resize(1, 3)
        .Value = .Value
    End With
    End If
Next lCounter
ThisWorkbook.ActiveSheet.Range("A5:O310").Select
    Selection.Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
ThisWorkbook.ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True, AllowFiltering:=True, Password:="password"
ThisWorkbook.ActiveSheet.Range("B5").Select
ThisWorkbook.Sheets("Open").Select

End Sub

--Karan--

many thanks, will give it a go.

Anytime

Ok, sort of works. The problem is – if I have 2 workbooks open (both with the same code) and I click on the big red X – and select “Yes to all” on the save option it still flags up and error on one of the workbooks.

Originally Posted by martindwilson

now did you bother to read the link i gave you?

Yes, but I must have not set the coding up specifically.

Karan's coding worked, but I still encounter an error when closing and saving multiple documents