Results 1 to 2 of 2

how to close a file opened with "Workbooks.Open Filename:=sWb"

Threaded View

  1. #1
    Registered User
    Join Date
    10-11-2010
    Location
    US
    MS-Off Ver
    NA
    Posts
    41

    Cool how to close a file opened with "Workbooks.Open Filename:=sWb"

    I am looking to (1) close this file and (2) simplify\clean up the overall macro. Any recommendations.



    ****************************************************

    Option Explicit
    Sub UpdatePivotTables()
    '
    ' UpdatePivotTables Macro
    ' Macro recorded 2/28/2011 by Gary Gremel
    ' Keyboard Shortcut: Ctrl+Shift+U
    
    ' Prompts User to select file and import into Automated Report
        
        Application.ScreenUpdating = False
        Dim sFil   As String
        Dim sTitle As String
        Dim sWb    As String
        Dim iFilterIndex As Integer
    On Error GoTo err_handler
        ' Set up list of file filters
        sFil = "Excel Files (*.xls),*.xls"
        ' Display *.xls by default
        iFilterIndex = 1
        ' Set the dialog box caption
        sTitle = "Select this week's BRT Weekly Resource Report."
        ' Get the filename
        sWb = Application.GetOpenFilename(sFil, iFilterIndex, sTitle)
        Workbooks.Open Filename:=sWb
        
    ' Copies BRT Report from selected file to Weekly Resource Report.
        Sheets("BRT Report").Select
        Range("A4:BB3000").Select
        Selection.Copy
        Windows("Weekly Resource Report.xls").Activate
        Sheets("BRT Report").Select
        Range("A4").Select
        ActiveSheet.Paste
     
    ' Copies No of Wkdays Calc from selected file to Weekly Resource Report.
        Workbooks.Open Filename:=sWb
        Sheets("No of Wkdays Calc").Select
        Range("A1:BB3000").Select
        Selection.Copy
        Windows("Weekly Resource Report.xls").Activate
        Sheets("No of Wkdays Calc").Select
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    
    ' Updates the Pivot table Cache and data being reported in the Weekly Resource Report.
        Sheets("Pivot by Division Detail").Select
        ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
        Sheets("Pivot by Division").Select
        Range("C13").Select
        ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
        Sheets("Pivot by Region").Select
        Range("D8").Select
        ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    
    ' Return to instruction tab
        Sheets("Instructions").Select
        Range("A1").Select
        Exit Sub
    err_handler:
    MsgBox "No selection made"
    
    
    
    End Sub
    Last edited by ggremel; 03-07-2011 at 08:37 PM.

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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