is there a way to make a pivot either i) remain static with a range or ii) ensure it always grabs all the data when the pivot is refreshed in a mcaro with 'Activeworkbook.RefreshAll'
i.e. If I have 5000 rows today and my pivot range is Sheet1!$A$5:$T$5000 it will work.
if tomorrow the numberof rows has gone to say 4500 the range changes to 'Sheet1!$A$5:$T$4500'
if the following day the number of rows increases to 5100 the pivot range stays at 'Sheet1!$A$5:$T$4500' and will return invalid data.
So, I need to always ensure that I pick up all the data if the number of rows goes down or up