Name Range with Pivot Table and Dynamic Data

[davidmo]

Hey everyone:
I have multiple pivot tables based on one worksheet (Master Deduction). The Master Deduction is updated once a month and then the pivot tables need to be refreshed.

This is being created for a person that is not comfortable with pivot tables and excel (a beginner). I wanted to create just a control button that when clicked will refresh all the pivot tables without the person having to go into each table to change the data range.

I thought I could do this by basing the pivot tables on a name range (called "DCFRange") that can be used by all the tables. I found a vba code that will "grow" the DCFRange with each additional monthly data and then refresh all the pivot tables. I put the control button on a different worksheet within the workbook than the pivot tables and master data sheet.

Here is the code:
Sub Update_All_Reports()
LastRow = Worksheets("Master Deductions").UsedRange.Rows.Count
LastColumn = Worksheets("Master Deductions").UsedRange.Columns.Count
ActiveWorkbook.Names.Add Name:="DCFRange", _
RefersToR1C1:="=Master Deductions!R1C1:R" & LastRow & "C" & LastColumn
ActiveWorkbook.RefreshAll
End Sub

What is happening is the DCFRange only changes for the worksheet that the control button is located on. The pivot tables doesn't change, it keeps the original range. When I'm in the worksheet of one of the Pivot Tables, I look up the Name Range and it shows the original cell ranges.

Not sure what is going or why this would happen.

thanks.

DMo

 

[Gavona]

If Master Deduction is updated rather than created afresh AND your data follows good practice, ie:
1. no totally blank rows and columns within it
2. no data in row/column adjacent to database
then try using the CurrentRegion property to re-define the name.

Range("DCFRange").name = Range("DCFRange").CurrentRegion

And then reference the pivot table collection and refresh (or does Refreshall do that?. You may need to incorporate code to finetune the formatting after refresh - eg column widths.




Gavin

 

[SkipVought]

Hi,

You do NOT need VB code to use dynamic range defining.

How can I rename a table as it changes size faq68-1331

Check out the OFFSET function. For PivotTable source data I often use Database as the range name. Use that name in the Source Data window of the PivotTable Wizard.

Skip,

_[red][/red]

 

[KenWright]

Other instructions on how to create a dynamic range:-

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

For the refresh, if you have a number of tables and the data may need refreshing whilst the file is already open, then the following will update all the pivot tables inw hatever file it is run in:-

Private Sub RefreshTables_Click()
Dim ws As Worksheet
Dim pt As PivotTable
 
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
   For Each pt In ws.PivotTables
     pt.RefreshTable
   Next
Next
End Sub

Regards
Ken.............


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------