Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_change or Worksheet_calculate
Hi,
I have code to change the field values in all pivot tables on Sheet1 based on the values entered in some specific cells. On cell A2 I have a formula and I need the code to be triggered when it recalculates so it will change the pivot table field value. The problem is that the code I have works perfectly when the value is manually entered but not when it changes based on a formula. Also tried the Worksheet_Calculate () event but I don't know how to prevent the "Object Required" error. Would greatly appreciate the help!! Code is pasted below: Private Sub Worksheet_change(ByVal target As Range) Dim a(10, 2) As Integer Dim fieldadd(10, 2) As String fieldadd(1, 1) = "$A$2" fieldadd(1, 2) = "Name" Application.ScreenUpdating = False If (target.Address) = fieldadd(1, 1) Then For Each pt In ActiveSheet.PivotTables For Each pt1 In ActiveSheet.PivotTables pt1.PivotFields(fieldadd(1, 2)). _ CurrentPage = target.Value Next pt1 On Error Resume Next Next pt End If target.Select Application.ScreenUpdating = True End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet_Change vs Worksheet_Calculate | Excel Programming | |||
Re : Excel Worksheet_Calculate() & Worksheet_Change() | Excel Programming | |||
Worksheet_Calculate or Worksheet_Change? | Excel Programming | |||
Nesting worksheet_Calculate inside worksheet_CHANGE | Excel Programming |