Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

Macro for Auto Refreshing a pivot table every 30 secs for example

could someone please tell me how to write the VBA code to automatically update a pivot table every 30 secs?  much apprecitated.

Jason
0
jason1234
Asked:
jason1234
  • 3
1 Solution
 
Dave BrettVice President - Business EvaluationCommented:
You could try

Cheers
Dave

Public RunWhen As Double
Public Const cRunIntervalSeconds = 30
Public Const cRunWhat = "Update_PivotTable"

Sub auto_open()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
    schedule:=True
End Sub

Sub Update_PivotTable()
For Each Pt In ActiveWorkbook.PivotCaches
Pt.Refresh
Next
auto_open
End Sub
0
 
Dave BrettVice President - Business EvaluationCommented:
Whoops, needed a close routine to turn the OnTime routine off. Otherwise the file reopens


Public RunWhen As Double
Public Const cRunIntervalSeconds = 3
Public Const cRunWhat = "Update_PivotTable"

Sub auto_open()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
    schedule:=True
End Sub

Sub Update_PivotTable()
For Each Pt In ThisWorkbook.PivotCaches
Pt.Refresh
Next
auto_open
End Sub

Sub auto_close()
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
    schedule:=False
End Sub
0
 
jason1234Author Commented:
Thanks v. much..... works a treat!

again - thanks.
0
 
Dave BrettVice President - Business EvaluationCommented:
No problem Jason :)

Can you please close it off by accepting my second comment as an answer

Cheers

Dave

0
 
dbase118Commented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

PAQ \ Answered by brettdj
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

dbase118
EE Cleanup Volunteer
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now