How to Reference ONE cell from ALL Pivot Tables in a Workbook - VBA

[actjfc]

Excel friends,

The code wks.Range(rngAddress).Activate is not working. It generates a runtime error 91. Can somebody help me. Thanks!

Sub PivotTableMacro()
Dim wks As Worksheet
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim pt As PivotTable
Dim rngAddress As Range

For Each wks In wb.Sheets
For Each pt In wks.PivotTables
         
               rngAddress = Chr(34) & Left(pt.TableRange1.Address, InStr(1, pt.TableRange1.Address, ":") - 1) & Chr(34)
                 
               wks.Range(rngAddress).Activate 'I need this code to activate or select the cell rngAddress 
                 
               Application.Run "SpecialMacro" ' this Special Macro macro will run only if one cell is selected inside the current pt.

               MsgBox rngAddress ' for example, it shows "G10"

Next pt
Next wks
 
End Sub

Thanks again!

 

[DanteAmor]

Try this

Sub PivotTableMacro()
  Dim wks As Worksheet, wb As Workbook, pt As PivotTable, rngAddress As Range
  Set wb = ActiveWorkbook
  For Each wks In wb.Sheets
    wks.Select
    For Each pt In wks.PivotTables
      Set rngAddress = pt.TableRange1 '.Address
      rngAddress.Cells(1, 1).Select
      MsgBox rngAddress.Cells(1, 1).Address ' for example, it shows "G10"
      'Application.Run "SpecialMacro" ' this Special Macro macro will run only if one cell is selected inside the current pt.
    Next pt
  Next wks
End Sub

 

[actjfc]

Thanks! It works perfect!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.