Help with Selecting Ranges in Pivot Table

[pikapika13]

Much thanks in advance:
I have a pivot table, "Pivot1". For example sake, in column A I have
list of countries. In columns B through C, I have other attributes
that tie to column A (ie. cities, provinces, states, etc.) Note:
Subtotals in 'Field Settings' is set to NONE.

Pivot1
|ColumnA|----|Column B|-------|Column C|
USA-----------Illinois-------------Chicago
-----------------------------------Springfield
-----------------------------------East St. Louis
--------------Virginia-------------Richmond
-----------------------------------Midlothian
Canada ------Alberta-------------Edmonton
------------------------------------Calgary
Country3-----District-------------City1
------------------------------------City2



In another sheet, say "Sheet1" I have cell A1 that contains a country
name.

I would like a macro that selects the ranges in Pivot1 based on the
inputs in Sheet1!A1. I can't use a VLOOKUP because Column A has some
blanks.


In other words, in the example above, if A1 has "USA", I want the macro
to select everything from "USA" all the way down and over to
"Midlothian."

 

[Guest]

Look at the GetPivotData worksheet function. See Help for details.

 

[pikapika13]

I looked into GetPivotData, but that will only give me the subtotals. I
have text (instead of numbers) that need to be selected (to be copied
somewhere else later).

 

[Tom Ogilvy]

Nothing to stop you from looping through the data and getting the
information you need.

also use the find method of the range object. See Excel VBA help for
details