My workbook has a lot of different information in text and numbers.
Ex : A1 to A 50 are last names
B1 to B 50 are first names
C1 to c 50 colors
D1 to D 50 fruits. Going on from A to M down to 50 in every
worksheet (30).
They repeat a lot and I want to be able to do this :
Pick an item in a cell ( ex. grape, in fruit column) and PRINT all the ROWS
with grapes on it in one page( including first and last name, colors... for
that row).
Can this be done with pivot table? I'm not much related to it.
If there is no other function or formula , then my last choice will be a
MACRO if somebody has one. Thanks.

Try DataFilterAutofilter and filter for "grapes" in column D

When just rows with grapes are visible, print the visible cells.

This has to be done on a sheet by sheet basis.


Gord Dibben MS Excel MVP


On Sat, 7 Jul 2007 17:42:01 -0700, FC wrote:

My workbook has a lot of different information in text and numbers.
Ex : A1 to A 50 are last names
B1 to B 50 are first names
C1 to c 50 colors
D1 to D 50 fruits. Going on from A to M down to 50 in every
worksheet (30).
They repeat a lot and I want to be able to do this :
Pick an item in a cell ( ex. grape, in fruit column) and PRINT all the ROWS
with grapes on it in one page( including first and last name, colors... for
that row).
Can this be done with pivot table? I'm not much related to it.
If there is no other function or formula , then my last choice will be a
MACRO if somebody has one. Thanks.

You sure got it! Simple as that. Thanks Gord.

"Gord Dibben" wrote:

Try DataFilterAutofilter and filter for "grapes" in column D

When just rows with grapes are visible, print the visible cells.

This has to be done on a sheet by sheet basis.


Gord Dibben MS Excel MVP


On Sat, 7 Jul 2007 17:42:01 -0700, FC wrote:

My workbook has a lot of different information in text and numbers.
Ex : A1 to A 50 are last names
B1 to B 50 are first names
C1 to c 50 colors
D1 to D 50 fruits. Going on from A to M down to 50 in every
worksheet (30).
They repeat a lot and I want to be able to do this :
Pick an item in a cell ( ex. grape, in fruit column) and PRINT all the ROWS
with grapes on it in one page( including first and last name, colors... for
that row).
Can this be done with pivot table? I'm not much related to it.
If there is no other function or formula , then my last choice will be a
MACRO if somebody has one. Thanks.

If you want to get a little fancy, you can try this bit of code...

I found it online. You would paste it as code in the work sheet (right
click on the tab and got to view code/ paste it there)

This code will check if a target cell on the selected sheet was
changed (Key tab cell G10 in the example below)

if so, it goes to selected tabs in the workbook and sets the filter
values = to what the user selected.

the code can be easily modified to cycle through all the worksheets in
your book to set the same filter value on each tab.


Private Sub Worksheet_Change(ByVal Target As Range)

' Function sets filter value to equal Cell G10 on YTD and Quarter
Tonnage Customer Data Tabs
' When user selects a new value from the drop down
' Macro recorded 11/1/2006 by Tim Bradshaw


'Check to see if G10 was changed?
If Target.Address = "$G$10" Then

'if so, turn off screen updating to avoid flicker
Application.ScreenUpdating = False

'GEt the value the user entered
userinput = Range("Key!G10").Value

'Set the filter on the YTD Tab. First unprotect s/s then apply
filter then re-protect sheet.
Sheets("YTD Tonnage Customer Data").Select
ActiveSheet.Unprotect
Selection.AutoFilter Field:=1, Criteria1:=userinput
ActiveSheet.Protect AllowFiltering:=True


Sheets("Quarter Tonnage Customer Data").Select
ActiveSheet.Unprotect

Selection.AutoFilter Field:=1, Criteria1:=userinput
ActiveSheet.Protect AllowFiltering:=True

'Go back to the Key tab
Sheets("Key").Select

'Turn on screen updating
Application.ScreenUpdating = True

End If
End Sub

Do the following:

On the Data menu, point to Filter, and then click Advanced Filter

Even you have missplelled words you can Extract, Transfer the list to a
different location in your currect worksheet and print by selection.

For example, if you mispel Grapes as Garpes or Grape, you must filter three
times to get the actual count. Therefore use Advanced Filter to query the
list with appropriate wild character - like G*, to retrive all words starting
with G...

Challa Prabhu


"FC" wrote:

My workbook has a lot of different information in text and numbers.
Ex : A1 to A 50 are last names
B1 to B 50 are first names
C1 to c 50 colors
D1 to D 50 fruits. Going on from A to M down to 50 in every
worksheet (30).
They repeat a lot and I want to be able to do this :
Pick an item in a cell ( ex. grape, in fruit column) and PRINT all the ROWS
with grapes on it in one page( including first and last name, colors... for
that row).
Can this be done with pivot table? I'm not much related to it.
If there is no other function or formula , then my last choice will be a
MACRO if somebody has one. Thanks.