Hide actions while macro executes
 

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

Hide actions while macro executes
 

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)



"Ben Rum" wrote in message
. ..

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

Hide actions while macro executes
 

Cool, will give that a go.

I've been on your website recently, excelllent stuff..!

Thanks
Ben



Chip Pearson wrote:
Ben,

Use

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