Excel 2007 : Dynamic Named Range

[nirvehex]

Hello all,

I'm trying to create a dynamic named range to use as a source for my pivot table data. I've attached my xlsm doc to make it easier to follow. Please refer to this post.

Essentially, I have a sheet titled, 'Long'. Towards the bottom under week ending 7/22 from cells B128:T129 I need to make it a named range to act as a source for my pivot table. Each week I post a new data set so for example next week ending, 7/29 the named range would be from cells B161:T162.

So my question is, in the Refers to box of a named range, what code do I write so it keeps changing?

Thanks,

Mark