0

For instance I have a pivot table on every third worksheet that needs to be edited, on the worksheet is 3 pivot tables, I don't actually know the name of the table but I know it lies on $N$2, is there anyway to get the name or handle of the pivot table from the cell?

JJRhythm
  • 633
  • 3
  • 10
  • 16

1 Answers1

1

The Range object has a PivotTable property that returns the object you want

Set pt = MySheet.Range("N2").PivotTable
Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73
  • I think this should work except I am getting an error, Unable to get the PivotTable property of the Range class. – JJRhythm Nov 02 '10 at 16:20
  • Make sure your sheet reference is correct. Don't use an unqualified Range reference like Range("N2") because that will point to the activesheet which may not be what you want. To test, go select the range that you're referencing, go to the Immediate Window (Ctl+G) in the VBE and type ?ActiveCell.PivotTable.Name If you don't get an error there, it's your sheet or range reference in your code. You can edit your question and post the code you're using also. – Dick Kusleika Nov 02 '10 at 18:03
  • Hey thanks a lot I realized my problem from this, I was looking at N2 which is just a label above the pivot-table not actually in the table. I needed to switch it to N3, of course realizing this after I did the work manually. Oh well thanks a lot though I didn't know about the immediate window. – JJRhythm Nov 03 '10 at 04:07