Update pivot table filter based off cell entry

[Mattyd2242]

Hello there. I'm currently working on a database, for non excel users. Currently both of my tabs are connected to an Access database, which populates and refreshes the data every time the document is opened. Ideally, I'm trying to have what the user enters in cell E1 on the Query Sample tab filter the "vendor" pivot table row to on the PivotTable tab. My code is in VBA, but I have included it as well. Really any advice or another way to accomplish this would be extremely appreciative. Below is the current code.

Well I'm trying a different method here. The cell or range I'm looking to filter the pivot table off is G1. In trying to debug this, I'm getting a subscript out of range error at the line, Set pvtTable = Worksheets("PivotTable").PivotTables("PivotTable1"). I've attached the workbook I'm currently working with. The cell I'm trying to have trigger this is on the Sheet1 (E1). The pivot field I'm trying to have controlled by this cell is on sheet "PivotTable" and the field vendor. Any help/advice would be appreciated.

[CODE]Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("g1")) Is Nothing Then
Application.Run "changevalue"
End If

End Sub

Sub changevalue()

' UPDATES THE Site Name

Dim pvtTable As PivotTable
Dim pvtField As PivotField
Dim pvtItem As PivotItem
Dim filterName As String

Set pvtTable = Worksheets("Pivot Table").PivotTables("PivotTable1")
Set pvtField = pvtTable.PivotFields("Cand Submitter Org Name")

filterName = Worksheets("Pivot Table").Range("g1")

pvtField.CurrentPage = filterName
End Sub

[/CODE]

 

[Nebu]

Hi:

You can use slicers if you are using excel version 2010 and above , it will basically list out the name of all vendors as a list and the user can select the vendor he needs to see the underlying data for , the pivot will change accordingly , no need for formulas or macros.

Thanks
Nebu

 

[Mattyd2242]

I appreciate the response, and I do love the use of slicers. My fear in this project is that the list of vendors will grow to over 1,000 making the time to use these not worth it. If a cell controls both the information on the first and second tabs by vendor, I believe the usability will greatly improve.

 

[Nebu]

Hi Matty:

Then I believe macro is the only way for you, I tried running ur macro, but I guess you are using a different version of excel than mine so cud not proceed.

Rgds
Nebu