+ Reply to Thread
Results 1 to 3 of 3

Worksheets not closing when code is executed

Hybrid View

jo15765 Worksheets not closing when... 01-08-2012, 10:13 AM
Richard Buttrey Re: Worksheets not closing... 01-08-2012, 10:39 AM
jo15765 Re: Worksheets not closing... 01-08-2012, 11:00 AM
  1. #1
    Forum Contributor
    Join Date
    06-18-2010
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    546

    Worksheets not closing when code is executed

    I have the following code, but the problem is that the workbooks do not close when the code is executed. Ideally, I would like each workbook to close once the code has finished executing on that specific workbook.
    Public Sub Testing()
        Dim Varbooks
        Dim varBook
        Dim wb As Excel.Workbook
    
        Application.DisplayAlerts = False
    
        Varbooks = Array("Fire", "Ice", "Wind", "Mountain", "Sun")
    
            For Each varBook In Varbooks
                Set wb = Workbooks.Open(Location of workbook & varBook)
                With wb
                    .SaveAs Filename:="Enter File Name here"
                End With
    Next
    
        Dim fileName1
        Dim fileName2
        Dim strPath1 As String
        Dim strpath2 As String
        Dim whichPath As String
        Dim CurrentPath As String
        CurrentPath = ActiveWorkbook.Path
        On Error GoTo ErrorCatch
    
        fileName1 = "Enter file Name Here"
        fileName2 = "Enter file Name Here"
        
        varWorksheets = Array(fileName1, fileName2)
        
        Dim strPathArr()
        ReDim strPathArr(1 To 2)
        MsgBox varBook
        For Each varBook In Varbooks
            strPathArr(1) = "1st location to check for the varbook" & varBook 
            strPathArr(2) = "2nd location to check for the varbook" & varBook 
    
                For Each varWorksheet In varWorksheets
                Set wb = Nothing
                whichPath = InWhichPathArr(strPathArr, varBook, varWorksheet)
                If Len(Trim(whichPath)) > 0 Then
                    Set wb = Workbooks.Open(Filename:=whichPath & "\" & varBook & varWorksheet)
                End If
                If Not wb Is Nothing Then
                    Dim wks As Worksheet, qt As QueryTable
                    For Each wks In wb.Worksheets
                        For Each qt In wks.QueryTables
                            qt.Refresh BackgroundQuery:=False
                        Next qt
                    Next wks
                    Set qt = Nothing
                    Set wks = Nothing
                    Application.DisplayAlerts = False
                    wb.SaveAs Filename:="Location and filename to save as"
                End If
        Next varWorksheet
        GoTo ExitMacro
        
    ErrorCatch:
    MsgBox Err.Description
    
    ExitMacro:
    On Error GoTo 0
    
    Next
    
    Set wb = Workbooks.Open(Name & Location of workbook wantingn to open)
    
        For Each varBook In Varbooks
            On Error Resume Next
            Dim ws As Object
            Set ws = Nothing: Set ws = wb.Sheets(varBook)
            On Error GoTo 0
            If Not ws Is Nothing Then
                ActiveWorkbook.SaveAs Filename:="Location and filename to save as"
            End If
    
            Next varBook
    End Sub
    Can someone tell me what needs to be added/modified to make the workbooks close once the code has executed?
    Last edited by jo15765; 01-08-2012 at 10:28 AM. Reason: Left out some of the code

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Worksheets not closing when code is executed

    Once which code has executed?

    If you mean the first For...Next loop which opens a workbook then just add the 'ActiveWorkbook.Close' instruction as the last line of the loop.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    06-18-2010
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    546

    Re: Worksheets not closing when code is executed

    Sorry, I guess I should have specified. After the last For Next Loop.
    Next
    
    Set wb = Workbooks.Open(Name & Location of workbook wantingn to open)
    
        For Each varBook In Varbooks
            On Error Resume Next
            Dim ws As Object
            Set ws = Nothing: Set ws = wb.Sheets(varBook)
            On Error GoTo 0
            If Not ws Is Nothing Then
                ActiveWorkbook.SaveAs Filename:="Location and filename to save as"
            End If
    The problem I am having is that it will close the workbook that the worksheet is copied to, but it will not actually close the source workbook that is opened.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1