VBA: For Each wb In Workbooks - doesn't work

[thedogg]

I have a problem with "For Each wb In Workbooks". I want to close all workbooks which haven't met certain condition.

Dim wb As Workbook
For Each wb In Workbooks
wb.Activate
    If Left(wb.Sheets(1).name, 4) <> "BOM." Then
        wb.Close SaveChanges:=False
    ElseIf Left(wb.Sheets(1).name, 4) = "BOM." And wb.Sheets(1).Tab.Color = 49407 Or wb.Sheets(1).Tab.Color = 49407 Or wb.Sheets(1).Tab.Color = 5296274 Then
    wb.Close SaveChanges:=False
    End If
Next

Macro randomly close what should be closed. Sometimes it depends which workbook is as Workbook(1). Could you advise?

 

[steve the fish]

First thing to do is to put brackets around your Or conditions.

 

[steve the fish]

Then the workbook that runs the macro is part of the Workbooks collection so it could end up closing itself so put in something like:

For Each wb In Workbooks
    If wb.Name <> ThisWorkbook.Name Then
        'code
    End If
Next

 

[thedogg]

In general the code is taken from userform and addin. So no relation between the workbook. Your solution works but what If I want to close also the current workbook?

 

[Norie]

Have you stepped through the code and checked the condition(s) in the if statement to see why workbooks that shouldn't be closed are being closed?

 

[thedogg]

It has never happened. The problem is that whorkbook which should be closed haven't been closed. I have also tested with the solutions above and it still doesn't work properly. I have no idea why, I have been fighting with this for 5 hours.

 

[Norie]

Have you tried stepping through the code at all, just to see what's happening/not happening?

 

[thedogg]

I have done it. I couldnt observe the problem first but seems to be that if I run code 1 and then code 2, not together but as two separated macros, even not call code1, call code2 it works. But if I have both together it doesn't

Code1

For Each wb In Workbooks
wb.Activate
    If wb.Sheets(1).Tab.Color = 49407 Or wb.Sheets(1).Tab.Color = 49407 Or wb.Sheets(1).Tab.Color = 5296274 Then
        wb.Close SaveChanges:=False
    Else
    End If
Next

For Each wb In Workbooks
wb.Activate
    If Left(wb.Sheets(1).name, 4) <> "BOM." Then
        wb.Close SaveChanges:=False
    Else
    End If
Next

 

[thedogg]

Seems to be that code stops after wb.Close SaveChanges:=False.

 

[RoryA]

And you're sure the code isn't closing the macro workbook?