Create a pivot table based on filtered visible data?

Posted on 2009-04-15
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

    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
    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! :)
    LVL 1

    Author Comment


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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now