VBA code to auto refresh Pivot table

Do you mean you need to expand the no of rows the pivot table is using as it's source?

If you do i would suggest you convert the data on 'Trans sheet' to table format and then redoing the pivot table.

Then you should be able to use something like this.

Sheets("Masterfile").PivotTables(1).PivotCache.Refresh ' change Masterfile if that's not the actual name with the pivto table on it.