• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I wish to update my pivot filter automaticaly from another sheet in the workbook

C

cujimmi

New Member
I have created a report that shows available budget spend, using a drop down list I can show what is available by employee. A macro to remove all "0" lines runs automatically. One of the columns in the report shows the purchase order position and I wish to persons name in the drop down list to auto-populate the filter on a pivot table which only holds the purchase order data (same workbook separate sheet). Not a big deal as I can use the filter drop-down on the pivot table, however, it would be neat and more user friendly. Could I add some code to my auto update VBA? Below is my VBA to auto update. Many thanks for looking have scoured the net for days trying to solve!
Code: 
Private Sub Worksheet_Calculate()

Dim r As Range, cell As Range

On Error GoTo ErrHandler

Set r = Sheets("availbud bh").Range("k8:k327")

Application.ScreenUpdating = False

Application.EnableEvents = False

For Each cell In r

  If cell.Value = 0 Then

  cell.EntireRow.Hidden = True

  Else

  cell.EntireRow.Hidden = False

  End If

Next

ErrHandler:

Application.ScreenUpdating = True

Application.EnableEvents = True

End Sub
 
Last edited by a moderator:
I didn't quite understand your layout...sounds like you need to update the PT after hiding/unhiding the rows?
Code: 
Private Sub Worksheet_Calculate()

Dim r As Range, cell As Range

On Error GoTo ErrHandler

Set r = Sheets("availbud bh").Range("k8:k327")

Application.ScreenUpdating = False
Application.EnableEvents = False

For Each cell In r
  cell.EntireRow.Hidden = (cell.Value = 0)
Next cell

'Update PivotTable
ThisWorkbook.RefreshAll


ErrHandler:

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub
 
Thanks for quick reply however I have not explained myself very well. The report gets data from different sources, actual expenditure, budgeted expenditure and the purchase order information, I have attached unique identifiers to each of the three sources in order to look at the data on one report. In this report I look at the data by budget holder for example John Smith via a dropdown which select John Smith and only his budget codes are displayed. In the purchase order column the "committed expenditure" is a total. I have hyperlinked the data back to a pivot table that contains the purchase order data only. However, I have to manually select John Smith in the report filter to show the outstanding purchase orders for John Smith. What would be neat and user friendly would be when I selected John Smith on the report dropdown the filter in the pivot table would also become John Smith so when the hyperlink was activated the user would see the John Smith data. Sorry it is so long and I expect the answer will be very simple, thanks again
 
Instead of using a Data Validation dropdown, could you create a PivotTable slicer and then have user select from there? The slicer can be on a different page, the PT will get a new set of filtering. You could have a formula read the PivotTable page field to see what user was selected via Slicer, and use that to update any other formulas you have.
 
Thanks again for prompt reply, sounds interesting, however, not sure. The initial data dropdown selection is not on a pivot table just a simple worksheet. The pivot table only covers the purchase order detail on a different sheet. Where can I read more about "slicers". I have seen reference to them but they are a new concept to me.
 
cujimmi - take a look at this post I did over at Daily Dose of Excel:
http://dailydoseofexcel.com/archives/2014/08/16/sync-pivots-from-dropdown/

I'm working on some code that will let you do the same very quickly in the case that the PivotTables don't share the same data sources (meaning you couldn't use Slicers).

And also check out my code on the Contextures website at http://blog.contextures.com/archives/2013/06/18/update-multiple-pivot-tables-20130618/

Note that I have a simpler version of that code and if you have any problems just reply back here.
 
Thanks for replies, jeffreyweir has hit the nail on the head. My pivot table does not share the same data sources which means I can't use slicers. I have looked at slicers and will now use them. Look forward to the code being written. It seems simple but I suspect not.
 
thanks for your help, pointed me in the right direction. I have solved the problem in a different way as I can't use slicers(multiple data sets that can't be amalgamated into one). I attached a simple macro to my dropdown box, so when a different budget holder was selected that new budget holder was input into the pivot table selection field. The relevant line of code that works (I don't know if it is efficient and is bound to be improved on) is

Sheets("orders os").PivotTables(1).PivotFields("budget holder").CurrentPage = Sheets("M").Range("j24").Text

where the pivot table sits on "orders os" and the updated budget holder on sheet "M" cell "j24"

There is only one pivot the sheet "orders os" therefore (1) is OK, on sheet "M" I have used an index to produce the budget holders name at cell "j24".

I suspect that the line of code could be added to my worksheet calculate event to reduce the clutter, but at the moment it works.
 
You must log in or register to reply here.
Back
Top