Hide actions while macro executes


B

Ben Rum

I have an excel file with 8 worksheets. Each of these pulls data from a
SQL Server database.

I have created a Macro which refreshes these all individually & then
returns to the first worksheet.

It is possible to make this happen without seeing each worksheet get
selected, refresh, etc, keep the focus on the first sheet and update a
cell with the current action? i.e. "Refreshing Batch Update
workseet..." & so on. And after its completed to display "Refresh
completed at 1-dec-2006 08:15am"

Copy of Macros below

Help appreciated!




Sub RefreshAll()
'
' RefreshAll Macro
' Macro recorded 30-11-2006 by Ben Watt
'

'
Sheets("Summary").Select
Range("A6").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Batch Update").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Datawarehouse ETL").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Disk Space").Select
Range("A42").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
Sheets("Scheduled Jobs").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Scheduled Jobs Perf").Select
Range("A42").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Sheets("SQL DATA").Select
Range("A42").Select
ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh
Sheets("SQL LOGS").Select
Range("A42").Select
ActiveSheet.PivotTables("PivotTable6").PivotCache.Refresh
Sheets("Summary").Select
Range("A6").Select
End Sub
 
Ad

Advertisements

C

Chip Pearson

Ben,

Use

Application.ScreenUpdating = False
'''''''''''''''''''''''''''
' your code here
''''''''''''''''''''''''''
Application.ScreenUpdating = True


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top