Pivot Table - Hiding Rows
 

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").Pivot Items
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

Pivot Table - Hiding Rows
 

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").Pivot Items
p.Visible = (p.Name = myCell)
Next p

--
Regards,
Tom Ogilvy


"Richard" wrote in message
...
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").Pivot Items
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

Pivot Table - Hiding Rows
 

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").Pivo tItems
p.Visible = (p.Name = myCell)
Next p

--
Regards,
Tom Ogilvy


"Richard" wrote in
message
...
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").Pivot Items
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



.