combine parts of many worksheets in many workbooks

[david_g]

I am looking for a way to combine the data from many worksheets in
several (about 30) workbooks.
My employer requires me to record my activity for each client using
excel workbooks which has separate worksheets for each product. The
activity is always recorded from row 16 starting in column B through to
column G. Each row contains a mixture of entries derived from a list
box, a manually entered date and a text comment.
Column B is the client name from a listbax
Column c is the client code entered by vlookup from the data in column
B
Column D is the event date which is entered manually
Column E is the activity type from a list box
Column F is used by my employer, I leave it blank.
Column G is a comment or text input

There can be as many rows as there has been activity or contact with
any client using that product. I initially started with 6 products but
I now have more than 20, each with their own worksheet. The name of
the worksheet is the product code and is always 11 characters.
I would like to combine the worksheets for each product so that I can
use a pivot table or similar to determine which of my various products
is most profitable and which requires the most customer intervention.

I could copy and paste special into a blank worksheet but feel that it
would be like having a dog and having to bark oneself, or whatever the
saying is.
Any ideas for a newbie with only basic VBA and who only partially
(fractionally or less ) understood Ron de Bruins code

[steven1001]

you could try the following..
http://www.contextures.com/excelfiles.html#Pivot
look for the paragragh saying..
"PT0007 - Multiple Consolidation Ranges -- Create a normal pivot table from multiple ranges, by using MS Query to join the data..."