try defining your data ranges as Dynamic Named Ranges, you can then call them programatically and pass them to your pivot table.
I have done a lot with pivots and VBA - but not with power pivots, so can't say for sure how you might do this.

I do also recommend that you check out Microsoft PowerBI. It will link to an excel table, and allow the generation of multi-table reports with ease