Help with Selecting Ranges in Pivot Table

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

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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>