Updating data source and refreshing all pivot tables through VBA.

Hi all,

I have a spreadsheet containing several pivot tables, all based on the same data source. From time to time, the data source is updated is additional rows (column A never contains any empty cells), therefore I created the following macro to update the data source of all pivot tables as well as refreshing those:

Please Login or Register  to view this content.

However it seems to fail when applying the SourceName variable. I am not sure where the issue could lie, I used F8 and watched the variables but the macro seems to do something completely different than what at I am expecting (i.e. simply update the source of each pivot tables using range A1:AX1 to the bottom of the 'MasterSheet' worksheet).

Any help would be appreciated. Thanks.

PS: I also used

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

but this did not help.

Try this...

Please Login or Register  to view this content.

I got this to work with your Test5 workbook.

Please Login or Register  to view this content.

Originally Posted by Folshot

Could you please upload the version of the spreadsheet you now have? I have cut & pasted the last set of code you provided but still encounter the same issue

Here it is.

Remove the .CurrentRegion property and the 1 from D1

Please Login or Register  to view this content.

Please Login or Register  to view this content.