I need to change the external data source for pivots found on multiple
worksheets within the same workbook. Is there code that I could use to do
this in one click of a button.

Also, how do keep the number formatting for my pivots after I refresh them?
I tried just going to table options and selecting "preserve formatting" and
unselecting "autoformat table" but this doesn't maintain the number
formatting inside the pivot.