Results 1 to 4 of 4

Thread: VBA - close all workbooks in macro.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a template that pulls in data from 37 other workbooks each week and calculates info with charts. My macro does not close all of the 37 workbooks automatically b/c each book prompts for "save changes? etc". Its not a big deal to click no 37 times real quick, but I would like my macro to close them all for me. Is there an easy way to write this in a loop so I dont have to hard code this for all 37 book file names?

  2. #2
    Board Regular
    Join Date
    Jul 2002
    Posts
    136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    just put
    activeworkbook.close savechanges:=true

    after each file used
    i suppose thats the end of the loop

  3. #3
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Just to expand a little on the above.

    activeworkbook.close savechanges:=true
    >>This will close the workbook and save any changes.

    activeworkbook.close savechanges:=false
    >>This will close the workbook without saving any changes.

    If you need any help in implementing the above, post your routine to the board so that we can see exactly what you are doing.
    Richie

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    As far as a loop goes, try the code below which will avoid you having to activate each workbook then close it with ActiveWorkbook:-

    Public Sub CloseAllWorkbooks()
    Dim wb As Workbook

    For Each wb In Workbooks
    wb.Close False ' Or True if you want changes saved
    Next wb

    End Sub


    [ This Message was edited by: Mudface on 2002-12-15 14:23 ]

Some videos you may like

User Tag List

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
  •