none
Macro to close specific files RRS feed

  • Question

  • I have files open called WorkJohn1, WorkJohn2, WorkBeth3, WorkBeth5 and Test1.

    I would like a macro to close (and not save) only the files beginning with "Work". These files are all .xlsb files

    The macros I tried either come up with an error message or it closes all the open workbooks.

    Tuesday, April 4, 2017 1:51 PM

Answers

  • Try...

    Sub CloseWorkbooks()
    
        Dim Wkb As Workbook
        
        For Each Wkb In Application.Workbooks
            If UCase(Left(Wkb.Name, 4)) = "WORK" Then
                Wkb.Close SaveChanges:=False
            End If
        Next Wkb
        
    End Sub

    Hope this helps!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    • Marked as answer by xanthman4 Tuesday, April 4, 2017 4:09 PM
    Tuesday, April 4, 2017 2:15 PM

All replies

  • Try...

    Sub CloseWorkbooks()
    
        Dim Wkb As Workbook
        
        For Each Wkb In Application.Workbooks
            If UCase(Left(Wkb.Name, 4)) = "WORK" Then
                Wkb.Close SaveChanges:=False
            End If
        Next Wkb
        
    End Sub

    Hope this helps!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    • Marked as answer by xanthman4 Tuesday, April 4, 2017 4:09 PM
    Tuesday, April 4, 2017 2:15 PM
  • Nothing happens.

    When debugging, it skips over the Wkb.close saveChanges:=False and goes to Next Wkb until finally ending. 

    Tuesday, April 4, 2017 2:45 PM
  • Sorry, didn't use caps in "Work"

    this worked.

    Thanks.

    Tuesday, April 4, 2017 4:10 PM
  • Hi xanthman4,

    you not need to use loop and loop through all open workbooks.

    also no need to use Ucase and left function and compare the workbook name.

    you can do this using single line of code.

    Workbooks("BOOK1.XLS").Close SaveChanges:=False

    if you use loop then it will make code slow and take more time to close the file.

    you will not able to notice this time with few workbooks. but when you open many workbooks then you will notice this that it takes more time.

    Reference:

    Workbook.Close Method (Excel)

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, April 5, 2017 12:30 AM
    Moderator
  • Thanks!

    Friday, April 7, 2017 4:40 PM