Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

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

  1. #1
    Board Regular
    Join Date
    Sep 2015
    Posts
    148
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?

  2. #2
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    5,546
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

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

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

  3. #3
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    5,546
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

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

    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 Code:
    For Each wb In Workbooks
        If wb.Name <> ThisWorkbook.Name Then
            'code
        End If
    Next

  4. #4
    Board Regular
    Join Date
    Sep 2015
    Posts
    148
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 by thedogg; Feb 21st, 2018 at 07:57 AM.

  5. #5
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    73,725
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    0 Thread(s)

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

    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?
    If posting code please use code tags.

  6. #6
    Board Regular
    Join Date
    Sep 2015
    Posts
    148
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  7. #7
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    73,725
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    0 Thread(s)

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

    Have you tried stepping through the code at all, just to see what's happening/not happening?
    If posting code please use code tags.

  8. #8
    Board Regular
    Join Date
    Sep 2015
    Posts
    148
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 by thedogg; Feb 22nd, 2018 at 01:34 AM.

  9. #9
    Board Regular
    Join Date
    Sep 2015
    Posts
    148
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

  10. #10
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    31,841
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

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

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •