Help with Selecting Ranges in Pivot Table

  • Thread starter Thread starter pikapika13
  • Start date Start date
P

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