Dynamic range pivot tables in VBA

[EcoWise]

Hi

I'm a new VBA user and need to create a Pivot Table that uses data from a dynamic range. Also, the destination of the pivot table I want to be in a separate worksheet form the actual database (within the same workbook).

Having done some research, I have used the following code;

Option Explicit


Sub Pivot_with_Dynamic_range()


Sheets("ExpenditureBasicsPVT").Activate
Dim ExpenditureBasicsPVT As Worksheet
Sheets("DataEntry").Activate
Dim DataEntry As Worksheet
Dim PTCache As PivotCache
Dim pt As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Dim FinalCol As Long
Set ExpenditureBasicsPVT = Worksheets("PivotTable")


For Each pt In ExpenditureBasicsPVT.PivotTables
pt.TableRange2.Clear
Next pt
Sheets("DataEntry").Activate
Cells(1, 3).Select
FinalRow = DataEntry.Cells(Rows.Count, 3).End(xlUp).Row
FinalCol = DataEntry.Cells(Columns.Count, 1).End(xlToLeft).Column
Set PRange = DataEntry.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
Set pt = PTCache.CreatePivotTable(TableDestination:=ExpenditureBasicsPVT.Cells(3, 2), TableName:="PivotTable1")



End Sub

The Data is located in the "DataEntry" worksheet and begins at cell "C1."
The pivot table destination must be in the worksheet, "ExpenditureBasicsPVT".

The above code gave me an "Run-time error '9', Subscript out of range" error when I tried to run it.

Could any one shed some light on this error.

Many thanks!

 

[Mahesh_P]

Hi

Check this link

excelvbaandmacros.blogspot.in/2011/12/excel-vba-pivot-table.html

 

[ChuckNorris]

Best option is to name a dynamic range and use this as the DataSource for the PT.
In order to assign dinamic range to a name (say your source information has new rows from time to time), simply do this:
- go to name manager and create NEW;
- define it as per this standard (adjust the names and columns to your file): "=SheetName!$A$1:OFFSET(SheetName!$A$1,COUNTA(SheetName!$A:$A)-1,0)";
- use the given name as the datasource in your PT wizard;
- voilá! your PT datasource will Always take the correct range.