Creating Dynamic Range in Pivot Table

Question

I'm trying to change my fixed range (A1:G4193) to one that's dynamic due to the need for new data to be entered on a daily basis.

Here is my code:

Sub Create_Pivot()

Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String
Dim pf As PivotField

SrcData = ActiveSheet.Name & "!" & Range("A1:G4193").Address(ReferenceStyle:=xlR1C1)

Set sht = Sheets.Add

StartPvt = sht.Name & "!" & sht.Range("A1").Address(ReferenceStyle:=xlR1C1)

Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=SrcData)

Set pvt = pvtCache.CreatePivotTable( _
    TableDestination:=StartPvt, _
    TableName:="PivotTable1")

I highly appreciate any help. Thanks!

Does the number of columns change, or just the number of rows? Look into [this simple code](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) to determine the last row .. — OldUgly, Dec 28 '16 at 21:21

score 0 · Answer 1 · answered Dec 28 '16 at 21:53

Change your range to a variable - like RNG The calculate the range , how ever you plan to do it. Last row, last column, last cell address, etc Then make the code soething like this

    Lastrow = ActiveSheet..Range("B" & Worksheets("All_Data").Rows.Count).End(xlUp).Row
    RNG = "A1:"G" & Lastrow
    SrcData = ActiveSheet.Name & "!" & Range(RNG).Address(ReferenceStyle:=xlR1C1)

score 0 · Accepted Answer · answered Dec 29 '16 at 06:19

Assumption - your Pivot Table's dynamic range is changing by the number of rows added (or distracted), while the number of columns stays constant.

Instead of using ActiveSheet, try using referenced objects, like Set SrcSht = Worksheets("Sheet1") and then use that variable.

Try the code below (some of my other modifications are inside the code's notes).

Option Explicit

Sub Create_Pivot_DynamicRange()

Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As Range
Dim SrcData As String
Dim pf As PivotField

Dim SrcSht As Worksheet, LastRow As Long

' modify "Sheet1" to your sheet's name
Set SrcSht = Worksheets("Sheet1")

With SrcSht
    ' find last row with data in Column A (skip blank cells in the middle)
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    ' set source data to dynamic number of rows (string)
    SrcData = .Name & "!" & .Range("A1:G" & LastRow).Address(ReferenceStyle:=xlR1C1)
End With

Set sht = Sheets.Add

' set the start position directly to a Range (there's no need to use a String as a "middle-man")
Set StartPvt = sht.Range("A1")

Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=SrcData)

Set pvt = pvtCache.CreatePivotTable( _
    TableDestination:=StartPvt, _
    TableName:="PivotTable1")

End Sub

Creating Dynamic Range in Pivot Table

2 Answers2