vba code to close all workbooks except active workbook which has macro

[aman2059]

Hello,

Trying to figure out the vba code which can help me to close all opened workbooks except the active one which has macro. Below is my code. But it does not close any workbook.

Please help. Thank you in advance.

Sub CloseOtherWorkbook()

Dim WB As Workbook

Windows("Master file.xlsm").Activate

Application.ScreenUpdating = False
For Each WB In Application.Workbooks
    If Not (WB Is Application.ActiveWorkbook) Then
        WB.Close
    End If
Next
Application.ScreenUpdating = True
End Sub

 

[Andrew Poulsom]

Try:

If WB.Name <> ThisWorkbook.Name Then

 

[aman2059]

Hi Andrew,

Thank you so much. It worked.

One more thing I wanted to add on this. Currently, it gives prompt message to save or not to save before closing workbooks. I wanted all workbooks to save by itself and then close.

Could you please help.

Sub CloseOtherWorkbook()
'this is to close all workbooks - let's figure how to do it

Dim WB As Workbook

Windows("Master file.xlsm").Activate

Application.ScreenUpdating = False
For Each WB In Application.Workbooks
    If WB.Name <> ThisWorkbook.Name Then
        WB.Close
    End If
Next
Application.ScreenUpdating = True
End Sub

Try:

If WB.Name <> ThisWorkbook.Name Then

 

[aman2059]

Hi Andrew,

Thank you so much. It worked.

One more thing I wanted to add on this. Currently, it gives prompt message to save or not to save before closing workbooks. I wanted all workbooks to save by itself and then close.

Could you please help.

Sub CloseOtherWorkbook()
'this is to close all workbooks - let's figure how to do it

Dim WB As Workbook

Windows("Master file.xlsm").Activate

Application.ScreenUpdating = False
For Each WB In Application.Workbooks
    If WB.Name <> ThisWorkbook.Name Then
        WB.Close
    End If
Next
Application.ScreenUpdating = True
End Sub

Try:

If WB.Name <> ThisWorkbook.Name Then

 

[yky]

Sub CloseOtherWorkbook()
'this is to close all workbooks - let's figure how to do it

Dim WB As Workbook

Windows("Master file.xlsm").Activate
Application.DisplayAlerts = False
Application.ScreenUpdating = False
For Each WB In Application.Workbooks
    If WB.Name <> ThisWorkbook.Name Then
        WB.Close savechange:=True
    End If
Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[aman2059]

It worked. Thank you

 

[chrono2483]

Sub CloseOtherWorkbook()
'this is to close all workbooks - let's figure how to do it

Dim WB As Workbook

Windows("Master file.xlsm").Activate
Application.DisplayAlerts = False
Application.ScreenUpdating = False
For Each WB In Application.Workbooks
    If WB.Name <> ThisWorkbook.Name Then
        WB.Close savechange:=True
    End If
Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Hi There,

I was trying this code as well, however my script does not recognize this line:

WB.Close savechange:=True

I changed it to read: WB.Close savechange:=False, as I do not want it to save.

Anything I am doing wrong?

 

[yky]

It should be savechangeS.

 

[chrono2483]

It should be savechangeS.

Perfect - it works! Thank you yky!