Prevent screen flickering when opening a second book (in the same copy)

Question

Prefix My code opens an external book with an internal database with some information that should not be visible to the entire organization. I can open an external workbook and get all the data from PivotTable successfully.

Problem . When my code runs, the screen flickers for ~ 0.5 seconds to show another book.

Purpose : Do not have flickering on the screen when switching between books.

My code (corresponding section):

 Option Explicit Public Sub GetBudgetData_fromPivotTable(Budget_ShtName As String, Budget_PvtName As String) Dim BudgetWB As Workbook Dim PvtTbl As PivotTable Dim pvtFld As PivotField Dim strPvtFld As String Dim prjName As String ' ****** This is the Section I am trying to prevent from the screen to flicker ****** Application.ScreenUpdating = False Application.DisplayAlerts = False ' read budget file parameters Set BudgetWB = Workbooks.Open(BudgetFile_Folder & BudgetFile_wbName) BudgetWB.Windows(1).Visible = False OriginalWB.Activate ' <-- this is the original workbook that is calling the routine Set PvtTbl = BudgetWB.Worksheets(Budget_ShtName).PivotTables(Budget_PvtName) ' a lot of un-relevant code line BudgetWB.Close (False) ' close budget file OriginalWB.Activate ' restore settings Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub 

Answer 1

To minimize screen flicker, I think the following should work; it adds an extra step to hide ActiveWindow after disabling ScreenUpdating to open and hide the workbook until the visibility levels are reset. When I tried this, the tape seemed to shut off and activate, but the spreadsheet was left without flickering. Not sure if this is enough for you ...

 Public Sub GetBudgetData_fromPivotTable(Budget_ShtName As String, Budget_PvtName As String) Dim BudgetWB As Workbook Dim PvtTbl As PivotTable Dim pvtFld As PivotField Dim strPvtFld As String Dim prjName As String ' ****** This is the Section I am trying to prevent from the screen to flicker ****** Dim wbWindow As Window: Set wbWindow = ActiveWindow ' Freeze current screen Application.ScreenUpdating = False wbWindow.Visible = False ' read budget file parameters Set BudgetWB = Workbooks.Open(BudgetFile_Folder & BudgetFile_wbName) BudgetWB.Windows(1).Visible = False ' Reset current screen wbWindow.Visible = True Application.ScreenUpdating = True OriginalWB.Activate ' <-- this is the original workbook that is calling the routine Set PvtTbl = BudgetWB.Worksheets(Budget_ShtName).PivotTables(Budget_PvtName) ' a lot of un-relevant code line BudgetWB.Close (False) ' close budget file OriginalWB.Activate ' restore settings Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub 

Answer 2

Another job that will work if you like it. The idea is that you copy a sheet with a pivot table in your book, and you work from there. Make the sheet hidden and after you are ready to work, delete it.

 Option Explicit Sub ImportSheet() Dim wbBk As Workbook Dim wsSht As Worksheet Dim sImportFile As String Dim sFile As String Dim sThisBk As Workbook Dim vfilename As Variant Application.ScreenUpdating = False Application.DisplayAlerts = False Set sThisBk = ActiveWorkbook sImportFile = Application.GetOpenFilename( _ FileFilter:="Microsoft Excel Workbooks, *.xls; *.xlsx", Title:="Open Workbook") vfilename = Split(sImportFile, "\") sFile = vfilename(UBound(vfilename)) Application.Workbooks.Open Filename:=sImportFile Set wbBk = Workbooks(sFile) If SheetExists("MyPivotData") Then Set wsSht = wbBk.Sheets("MyPivotData") wsSht.Copy ThisWorkbook.Sheets("MyPivotData").Visible = xlSheetVeryHidden End If 'do your work 'then delete the added sheet wbBk.Close SaveChanges:=False Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub Private Function SheetExists(sWSName As String) As Boolean Dim ws As Worksheet On Error Resume Next Set ws = Worksheets(sWSName) If Not ws Is Nothing Then SheetExists = True End Function