VBA Pivot Table: Determining item from one field based on anotherfield item
Hi all,
I have a Table/Listobject with a task list. I'm summarizing and performing calculations using a Pivot table. I want to use the Pivot as an interface to the user so that I can manipulate the task list data (datasource) through code. For example, if the user selects a task on the pivot table, and clicks a button, the associated code would get the task name from the pivot table, and determine via code the task ID, so that it would be possible to change any task data in the table (status, due date, etc). Unfortunately, I haven't figured out how to grab the task ID from the task name. Here a sample code, I have tried out: -------Code start--------- Dim r As Range Dim pvt As PivotTable On Error GoTo errpart Set r = ActiveCell Set pvt = Worksheets("Task by Project").PivotTables("pivot_tasks") Idx = 0 If r.PivotField.Name < "Task" Then MsgBox "Selection is not a task.", vbCritical, "Error" Else For i = 1 To pvt.PivotFields("Task").PivotItems.Count s = pvt.PivotFields("Task").PivotItems(i).Name If s = r.PivotItem.Name Then Idx = i Next i If Idx 0 Then MsgBox pvt.PivotFields("UID").PivotItems(Idx).Name End If End If Exit Sub errpart: MsgBox "Selection is not inside table.", vbCritical, "Error" -----Code Finish---- With the code above, the ids I'm getting doesn't match the data in the datasource, which leads to the conclusion that the indexes from one Pivotitems collection from one pivotfield are not "synchronized" to the Pivotitems collection from another pivotfield. Please, any help is very appreciated. Regards, Alex |
All times are GMT +1. The time now is 03:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com