PDA

View Full Version : Solved: Non-Continuous Range with Pivot Table



JimS
03-29-2013, 11:00 AM
I have been all over trying to learn how to use a non-continuous data range as the source for a Pivot Table but I cannot figure out how to make it work - perhaps it won't.

I have a large list of data that I would like to only point to a subset of this data, using a dynamic range name, and use it as the source for a pivot table.

I will ultimately use the main list to create several smaller subsets as the source data for several pivots.

I'm able to create a range name that points to the subset of data but I would like to include the data header row if possible.

I'm using Excel 2007.

In the attachment is an example of the data.

Thanks for any ideas...

JimS

mdmackillop
03-29-2013, 01:30 PM
You could use the Filter property
Option Explicit

Sub FilterPivots()
Dim col As New Collection
Dim i As Long
On Error Resume Next
For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row
col.Add Cells(i, 2), Cells(i, 2)
Next
On Error GoTo 0

For i = 1 To col.Count

rw = 10 * i
pt = PivotTable & i

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R1973C4", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Data!R" & rw & "C7", TableName:=pt, DefaultVersion:= _
xlPivotTableVersion14
With ActiveSheet.PivotTables(pt).PivotFields("Area")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables(pt).AddDataField ActiveSheet.PivotTables( _
pt).PivotFields("Counts"), "Sum of Counts", xlSum
With ActiveSheet.PivotTables(pt).PivotFields("Group")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables(pt).PivotFields("Group").ClearAllFilters
ActiveSheet.PivotTables(pt).PivotFields("Group").CurrentPage = CStr(col(i))
Next
End Sub

JimS
04-01-2013, 10:48 AM
Thanks for the code. I'll give it a try.

JimS