You don't need a macro to update the source of a pivot table.

Put the source data in a table and create the pivot. The source expands and contracts with the table.