Macros in PERSONAL.XLS

[mdmaremont]

Excel 2003; Windows XP OS. I have a series of macros in my PERSONAL.XLS
workbook that create a number of different pivot tables. I can't set up to
run automatically, as I must create the datatables daily and they are a
different size each day. The macros must be executed in the order they were
created or they 'bomb'. When I look at the VBA code, each different macro
contains a numbered pivottable reference, reflecting its creation order (e.g.
PivotTable3). Is there a way to make the references valid regardless of the
order in which they are executed?

 

[Peter T]

When you create the PivotTable with code you have the opportunity to give it
your own name, record a macro and look at the arguments, eg
TableName:="<pivotTable-name>"

When you want to refer that PivotTable later

Dim pt As PivotTable
Set pt = ActiveWorkbook.Worksheets("Sheet1").PivotTables("myPvtTble")

Regards,
Peter T