Pivot Table - Hiding Rows

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

I have the following items:
1. A simplified database (DB) with 2 columns - column A:
Spare Parts ID (alphanumeric); column B: values. The DB
is the input to a Pivot Table (IDs to Row, values to
Data).

2. A macro that highlights all the items in the DB
(colorindex = 36) that have a specified ID. The
specified ID is obtained via an Inputbox.

3. A second macro hides all rows in the Pivot Table
except for a specified ID:

The second macro:

For Each p In ActiveSheet.PivotTables
("PivotTable1").PivotFields("SpareParts_ID").PivotItems
p.Visible = (p.Name = myCell)
Next p

Where "myCell" is an ID of a spare part and is obtained
via another InputBox.

After highlighting the specified ID in the database
(first macro), how do I hide all rows in the Pivot Table
except for the IDs highlighted in the database? In short,
the variable "myCell" in the second macro needs to relate
to the highlighted ID (first macro).

TIA

Richard
 
For each cell in Range("DB").Columns(1).cells
if cell.Interior.ColorIndex = 36 Then
myCell = cell.Value
exit for
end if
Next
For Each p In ActiveSheet.PivotTables
("PivotTable1").PivotFields("SpareParts_ID").PivotItems
p.Visible = (p.Name = myCell)
Next p
 
Tom,

Thank you very much.

Regards,

Richard
-----Original Message-----
For each cell in Range("DB").Columns(1).cells
if cell.Interior.ColorIndex = 36 Then
myCell = cell.Value
exit for
end if
Next
For Each p In ActiveSheet.PivotTables
("PivotTable1").PivotFields("SpareParts_ID").PivotItems
p.Visible = (p.Name = myCell)
Next p

--
Regards,
Tom Ogilvy





.
Click to expand...
 
Back
Top