Pivot table filter from cell value

I need a modification to a previous post. I've tried changing the code with no success. Where the code refers to Dim filterDate as Date, I need to refer this to cell which contains text.

My file has a pivot field which is a company name which I want to reference from a cell on another sheet. Once this cell changes I want the pivot table to refresh automatically. Any help will be appreciated.

This is the link to the previous post and a solution posted by T-J:

http://www.excelforum.com/excel-prog...ell-value.html

Please Login or Register  to view this content.

You can use a Worksheet_Change event procedure to trigger the macro; however there are some potential problems in the code example that you referenced.

I'll suggest some alternative code if you'll provide a little more info:

1. What area of the Pivot Table Report is the Company Name field placed (either PageFields, Row Labels, or Column Labels)?

2. When you say you want the PivotTable "refreshed", do you just want the Filter changed, or do you also want to Refresh the PivotCache from the DataSource?

Thanks for responding.

My company name is a row field. The source data may change to include a new company. If I select the company in the pivot I still need to refresh the table to make the data show so auto refresh of data will be needed.

First, place this furnction in a Standard Code Module in your Workbook....

Please Login or Register  to view this content.

Then add this untested code to the Sheet Code Module of the Sheet that has the Company Name Text that will be changed by the user. (Edit the Sheet, PivotTable, PivotField and Address references to match yours).

Please Login or Register  to view this content.

The code has stopped in Private Sub Worksheet_Change(ByVal Target As Range) at:

Set PT = Sheets(sSheet).PivotTable(sPTName)

My mistake. It should read:

Please Login or Register  to view this content.