Pivot Table - Dynamic Source Data

[shendik]

I know this is not a new issue, yet I am having with finding a solution. I am using pivot tables/charts for drilldown reporting. After running a complex macro to consolidate multiple reports into one range, I need to generate pivot tables and charts.

These reports will change month to month in the number of entries they have (template will be identical), and therefore my data source range for the pivot table is bound to change. Therefore my question is, how to develop a macro that will dynamically change the source data range to the amount of rows?

Please let me know the code or give me a reference to a discussion where the code is provided. I did come across a thread before, but the solution proposed did not work for me. Thanks!

 

[Jerry Sullivan]

Have you considered using a Dynamic Named Range as the source for your PivotTable/ Chart?
Contextures' Site has a good explanation.

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

I can provide a VBA example if you prefer to go that route.

 

[Makpo]

Have you considered using a Dynamic Named Range as the source for your PivotTable/ Chart?
Contextures' Site has a good explanation.

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

I can provide a VBA example if you prefer to go that route.

Hi,

Your last line would be the answer to my exact question. Am not sure if you are online, but could you pls help me out with the vba example. I would like to go via that route.

 

[shendik]

Thanks for the help JS411! I went to the link referred, but the issue was that the instructions provided were for Excel 2003 user interface.

After reading your message, I decided a simple way to tackle this issue would be to create the range name. I didn't even integrate it with a macro. All I did was define the desired range with a name called "pivotinput". I used this range name as an input source data for the different pivot tables.

When I have to run different reports, I go into the "Formulas" tab, click on "Name Manager" and change the "pivotinput" data range to the new desired range. By doing this, all pivot tables have the updated data range. This saves a lot of name and redundancy.

Although this works great, as the last person pointed out, it would be great if you could provide a VBA example and even automate this manual part of going into the name manager and changing the desired range every time. Thanks a lot.

 

[Jerry Sullivan]

After reading your message, I decided a simple way to tackle this issue would be to create the range name. I didn't even integrate it with a macro. All I did was define the desired range with a name called "pivotinput". I used this range name as an input source data for the different pivot tables.

When I have to run different reports, I go into the "Formulas" tab, click on "Name Manager" and change the "pivotinput" data range to the new desired range. By doing this, all pivot tables have the updated data range. This saves a lot of name and redundancy.

It's great to see that you found your way to that process.
What you describe is 90% the same as the method of using the dynamic named ranges as Pivot data source that is described at the link.
The only difference, is that if you make your range dynamic, you can eliminate the step of redefining "pivotinput" through the Name Manager- it will update by itself. Sorry that you found the xl2003 instructions hard to follow for xl2007. I'm sure you will find instruction for xl2007 if you do a Google Search for "Dynamic Named Range"

Nonetheless, below is some VBA code as requested by you and Makpo.

It assumes:
1. Your data source is in the same workbook.
2. The upper-left cell of your data source hasn't moved from it's previous address.
3. It will use CurrentRegion as the new data source and will show an error message if that is not a valid PivotData source (for example if there are blank cells in the header row, or only one row of data).

I'd still encourage you to try to use dynamic named range. It's a better method than this VBA approach.

Sub Update_PT_Data_Source()
    Dim PT As PivotTable
    Dim sAddA1 As String
    Dim sAddress() As String, sAddNew As String
    On Error GoTo ErrorHandler
    
    For Each PT In ActiveSheet.PivotTables
        With PT
            If InStr(.SourceData, "[") > 0 Then
                MsgBox "The data source for PivotTable: " & .Name & _
                    "is not in this workbook. "
            Else
                sAddress = Split(Replace(.SourceData, "!", ":"), ":")
                sAddA1 = Application.ConvertFormula(sAddress(1), xlR1C1, xlA1)
                sAddNew = Sheets(sAddress(0)).Range(sAddA1) _
                    .CurrentRegion.Address(ReferenceStyle:=xlR1C1)
                .SourceData = sAddress(0) & "!" & sAddNew
                .RefreshTable
            End If
        End With
    Next PT
    Exit Sub
ErrorHandler:
    MsgBox Err.Number & "-" & Err.Description
End Sub

 

[shendik]

Excellent! Thank you very much. I successfully used the "dynamic named range" without having to use VBA. It is an elegant solution!

Thank you for also posting the VBA code. Really appreciate your help.