How to select data range dynamically for pivot table

Question

I have searched this topic exhaustively however I am struggling to find a solution which works for my macro. I need the source data for a pivot table to include all rows (containing data) on a sheet. The amount of rows will change daily.

Here is what I've got so far:

    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "RAW_DATA"
    Range("A1").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "RAW_DATA!R1C1:R159C24", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion14
    Sheets("Sheet4").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Asset")
        .Orientation = xlRowField
        .Position = 1
    End With

The values which represent my pivot tables source data are RAW_DATA!R1C1:R159C24. The problem is that I need this range to dynamically increase or decrease depending on the size of the populated source data range.

Use a Dynamic Named Range to define your data table, then have the PIvot Table source set to this named range. Once you set it up once, you will not need VBA at all to update it. — Scott Holtzman
– Scott Holtzman, Commented Feb 4, 2016 at 16:18
No need for named ranges, just select the columns only and that's it. — Rik Sportel
– Rik Sportel, Commented Feb 4, 2016 at 16:41

Rik Sportel · Accepted Answer · 2016-02-04 16:34:58Z

First of all, you might be able to easily solve your problem by just setting the columns as your datarange (E.g. RAW_DATA!$A:$X). When the data is appended a simple update on the pivot will include new data or exclude the data that is no longer there.

That said, here's a VBA solution: This Example will change the source data for PivotTable1 on Sheet1 to be "RAW_DATA!$A$1:$X$ whatever the last row is"

Sub ChangePivotData()
Dim lastrow as double

lastrow = Worksheets("RAW_DATA").Range("A" & Rows.Count).End(xlUp).Row

With Worksheets("Sheet1").PivotTables("PivotTable1")
    .ChangePivotCache ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:="RAW_DATA!A2:X" & CStr(lastrow), _    
        Version:=xlPivotTableVersion14)
End With
End Sub

That is the core of it. However, you might want to check for blank column headers to do some error prevention, automatically refresh the table after the new cache has been set, etc.

More extensive example can be found here: http://www.thespreadsheetguru.com/the-code-vault/2014/7/9/change-a-pivot-tables-data-source-range

Enjoy :)

gbardon · Accepted Answer · 2016-03-01 17:05:44Z

0

You can use the below if your data is the only thing in the sheet

Worksheets("RAW_DATA").Usedrange

and the corresponding range address

Worksheets("RAW_DATA").Usedrange.Address

Hope this helps

answered Mar 1, 2016 at 17:05

gbardon

1

Collectives™ on Stack Overflow

How to select data range dynamically for pivot table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related