Dynamic Range Using Macro Recorder

[CoolBreeze]

When I record my macro, I click any cell within the data range and Excel automatically knows the range of the data. But, it records the top left to bottom right block of data with hard numbers. If I re-run the macro next month and the data has expanded, which it will, my recorded macro is hardcoded to the wrong range.

How do I get my macro to act like it does while I'm recording? i.e. how do I write code that will use excel's ability to 'know' my data range?

example of recorded code where R2C1:R3674C43 is offending code:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:="'Copy of matt~9th pass'!R2C1:R3674C43").CreatePivotTable _
TableDestination:="", _
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10

Thank you,
CoolBreeze

 

[Oaktree]

Set up a dynamic named range first, then reference it in your code.

See the "Dynamic Ranges" portion of Chip's writeup here for help setting up a dynamic named range:

http://www.cpearson.com/excel/named.htm