You can make it a Dynamic Range

http://www.contextures.com/xlPivot01.html

Then use this to auto refresh when the sheet tab is selected

http://www.vbaexpress.com/kb/getarticle.php?kb_id=80

HTH

VBA Noob