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."


--
pikapika13
------------------------------------------------------------------------
pikapika13's Profile: http://www.excelforum.com/member.php...o&userid=10892
View this thread: http://www.excelforum.com/showthread...hreadid=527236

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

--
Regards,
Tom Ogilvy


"pikapika13" wrote:


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."


--
pikapika13
------------------------------------------------------------------------
pikapika13's Profile: http://www.excelforum.com/member.php...o&userid=10892
View this thread: http://www.excelforum.com/showthread...hreadid=527236

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).


--
pikapika13
------------------------------------------------------------------------
pikapika13's Profile: http://www.excelforum.com/member.php...o&userid=10892
View this thread: http://www.excelforum.com/showthread...hreadid=527236

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

--
Regards,
Tom Ogilvy


"pikapika13" wrote
in message ...

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).


--
pikapika13
------------------------------------------------------------------------
pikapika13's Profile:
http://www.excelforum.com/member.php...o&userid=10892
View this thread: http://www.excelforum.com/showthread...hreadid=527236