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

thedogg

Board Regular
Joined
Sep 22, 2015
Messages
154
I have a problem with "For Each wb In Workbooks". I want to close all workbooks which haven't met certain condition.

Code:
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?
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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:

HTML:
For Each wb In Workbooks
    If wb.Name <> ThisWorkbook.Name Then
        'code
    End If
Next
 
Upvote 0
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?
 
Last edited:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Have you tried stepping through the code at all, just to see what's happening/not happening?
 
Upvote 0
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
Code:
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


Code:
For Each wb In Workbooks
wb.Activate
    If Left(wb.Sheets(1).name, 4) <> "BOM." Then
        wb.Close SaveChanges:=False
    Else
    End If
Next
 
Last edited:
Upvote 0
And you're sure the code isn't closing the macro workbook?
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top