Create a pivot table based on filtered visible data?

Posted on 2009-04-15
Medium Priority
Last Modified: 2013-11-25
Hi Experts-

I want to create a pivot table based only on what I have filtered.  For example, I filtered Column L (Field #12) to show only "state".  When I continue with the code, my pivot table still shows "county" and "city coming though as options for Column L (Field #12).  Any help would be appreciated.

Sub StateOnly()
With Sheets("AZ Data").Rows("10:10")
    .AutoFilter Field:=12, Criteria1:="State"
End With
    If Cells(65536, "L").End(xlUp).Row <> 10 And Cells(65536, "L").End(xlUp).Row <> 11 Then
    Range(Selection, Selection.End(xlDown)).Select
    End If
'   Create the Cache
    Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Range(Selection, Selection.End(xlDown)).CurrentRegion)
'   Add a new sheet for pivot table
    Sheets.Add after:=Sheets("AZ Data")
    ActiveSheet.Name = "AZ Pivot"
'   Create the Pivot Table
    Set PT = ActiveSheet.PivotTables.Add(PivotCache:=PTCache, TableDestination:=Range("A1"))
End Sub

Open in new window

Question by:quaziblue
LVL 16

Accepted Solution

Jerry Paladino earned 1600 total points
ID: 24153250
Filtered data in a list is just non-contiguous rows displayed with the rows between them being hidden.  I have run several samples trying to get a pivot table to work with non-contiguous rows using named ranges as the pivot table data source.  It fails consistantly with an error of "Data Source Reference not valid".
I was successful when I filtered the data and then did a copy / paste into a new sheet and ran the pivot table from the new sheet.  Copy/paste with filtered data only brings across the visable rows to the destination.
As a suggestion you can try....  
In your VBA code, after you have applied the filter, copy the filtered data and place it on a new ("temp") sheet and use this new sheet as the data source for the Pivot Table.   Clear the sheet of any previous data before you paste and it should give you a clean set of data each time it's run based on the filters you apply.  If a Dynamic Named Range is defined for the ("temp") sheet then the pivot table could use the name range as as the data source each time and you don't have to worry about the size of the data being pasted.
I have not tried this in VBA before but based on the manual steps it seems it should be a viable solution.
LVL 85

Expert Comment

by:Rory Archibald
ID: 24156251
Why not just use all the data but change what is displayed in the pivot table? That is half the point of using a pivot table after all! :)

Author Comment

ID: 24327333

Thanks for the additional suggestion you included.  That's what I ended up doing.


Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question