Dave,

I must admit I did try to rework your original programme, combining it a
bit with the second programme you suggested in order to pull out the red
cells but this time listing them one at a time on the new sheet (instead
of the whole row).

I think I may have created a bit of a mess as I had reworked the line
to:

SumWks.Range("a1").Resize(1, 5).Value _
= Array("city", "store", "product", "year","sales")
(and also a few other lines)

What I was trying to do is - this is the dataset:

value of sales

city store product 1995 2000 2001 2002
Tampa Walmart shoes 126 222 378 498
NYC Macy television 33 26 78 76
Boston Walmart computers 257 876 343 798

Similar to the program pulling out all the lines with a red color in the
2nd column, how could the programme be reworked so that:

- the computer goes into the worksheet, goes into each cell and checks
for any red cells for sales values under the year field headings
- if the cell is red, then the sales value is extracted and listed in
the new sheet in the following form and under these field headings:
city store product year salesvalue
- go through each cell in the worksheet checking for red
- run through all of the worksheets in the workbook.
(field headings start on row 5 in every worksheet, years are always from
columns 4-7 across)

So, for example in the dataset above if the numbers (salesvalues) 222,
78, 76 and 343 were highlighted in red, in the new worksheet the
following field headings and list would appear:

city store product year value
Tampa Walmart shoes 2000 222
NYC Macy television 2001 78
NYC Macy television 2002 76
Boston Walmart computers 2001 343

I realized this would be an even more direct way of extracting the data
highlighted in red than the whole line at a time.

Hope I've been able to describe it clearly. Thanks.

Dave




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!