Prevent data source range for pivots from changing

I've made a macro for my team that basically inserts columns, does some calculations, and delete rows. It runs fine but the last step I want to include is refresh the pivot tables that are sourced to these data ranges. But every single time the data source range keeps changing and it makes the pivots error. For example, if my starting data is from A:N, and the macro adds columns to make it A:P, the pivot table source will suddenly change to A:S

I tried doing the named ranges with absolute ranges $A:$P and it still doesn't work. Using the offset formula to make a dynamic range doesn't seem to work either - in fact the offset formula kept changing to completely different columns so I stopped trying.


I think Excel is getting confused when a bunch of column changes are happening before refreshing. Is there any code to lock a data source range regardless of what happens in the macro? I’ve attached a sample workbook with my macro. I want the data to stay as A:P for the QV Pivot and A:U for the WHSE pivot. The code isn’t the cleanest but I have comments in what I’m expecting it to do. Any help or suggestions is appreciated