Excel VBA Pivot table Dynamic Range

Question

Good day,

I'm trying to get the range to create a pivot table. The Column amounts can differ each month and the rows. My code is not working at the marked section (Bold) to set the Pivot table range and I believe the line after that won't either to create the Pivot cache. Is there a better way to do it or to fix this code, please?

Dim RowsCount As Long, ColCount As Long
Dim wsStores As Worksheet
Dim pCache As PivotCache
Dim pTable As PivotTable
Dim PvtRange As Range
Dim lastRows As Long

Set wsStores = Worksheets.Add
RowsCount = Worksheets("Active Instances").Cells(14, 1).End(xlDown).Row
ColCount = Worksheets("Active Instances").Cells(14, Columns.Count).End(xlToLeft).Column
**PvtRange = Worksheets("Active Instances").Range(RowsCount, ColCount)**
Set pCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, PvtRange)
Set pTable = pCache.CreatePivotTable(wsStores.Range("A3"))

PvtRange is a Range object. You need to prefix it with the word "Set" like so: Set PvtRange = Worksheets("Active Instances").Range(RowsCount, ColCount). But even then, this code isn't going to work, as that just sets the range to a single cell, and you can't make a PivotTable out of a single cell. — jeffreyweir
– jeffreyweir, Commented Sep 9, 2017 at 20:14

Simone Evans · Accepted Answer · 2017-09-10 21:21:17Z

0

Thanks for the reply @jeffreyweir I managed to fix it with the following code:

Dim wsStores As Worksheet
Dim pCache As PivotCache
Dim pTable As PivotTable
Dim lastRows As Long

Set wsStores = Worksheets.Add
Set pCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, Sheets("Active Instances").Range("A14").CurrentRegion.Address(ReferenceStyle:=xlR1C1))
Set pTable = pCache.CreatePivotTable(wsStores.Range("A3"))

I didn't want to change from the pivot table to ListObject because the rest of the code (which was fine but not added into the question) was already set up without any problems. In the future, I will definitely try using the ListObject

answered Sep 10, 2017 at 21:21

Simone Evans

3111 gold badge4 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA Pivot table Dynamic Range

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related