Extending Data Range of a Pivot Table

Hi everybody

I am working with macros which Refresh the Pivot Table, automatically. Everything is working fine except one real problem, as below:

When the newer rows are added to the worksheet, Refreshing the Pivot Table does not include those added rows in the Pivot Table, automatically, unless I do it manually. Extending the Range manually kills the whole purpose of the macros.

Would a wizard help me getting around this problem!

Also, how to find the Pivot Table name to reference it. At the moment, I am using FOR loop to hit any Pivot Table available on the sheet.

Any expert help will be highly appreciated.

Regards,

For your first issue, if the worksheet contains only the data table for your pivot table, I would recommend you using a dynamic named range for the data. This way, you can simply refer to this range in your code, and it will update automatically. If you are unfamiliar with dynamic named ranges, have a look here:

http://www.contextures.com/xlNames01.html

For the second issue, the following will give you the name of each pivot table on the active sheet:

Sub pivotnames()
    Dim pt As PivotTable
    For Each pt In ActiveSheet.PivotTables
        MsgBox pt.Name
    Next pt
End Sub

HTH

Jason

Thanks Jason, for the tip.

Regards