Please forgive my convoluted description...

4 times per week, I receive 40 pages of data. Which I am supposed to copy and paste bits to build a matrix of sorts. To try to automate I dumped the data into excel and tried to isolate the cells that contain the information I need. Unfortunatly the data "header" is repeated 40 times and contains 40 different values so this was more work than just copy/paste. The solution is way beyond my skill level...

Any help would be greatly appreciated.

1. I need a vba code that will search for a series of 10 different key words in order and copy the contents of 1 cell directly below the keyword cell to a single row of a new sheet. Please note it is important that the data be grabbed as a series.

2. also the last keyword in each series will contain 10 content cells, rather than 1 cell, directly below the keyword cell.

3. loop to the "end",when there are 35 blank rows that means the end of the data.

Note: The data does not extend past column z or past row 1500. Don't know if that helps...

ANY help here is greatly appreciated!

I have attached a picture to help with what I'm trying desperatly to describe.

For Example:

below are the column/row locations for a 2 Keyword Series:
A4 = Go (Keyword #1)
A3 = Green
W3 = Stop (Keyword #2)
W4 = Red

C8 = Go (Keyword #1)
C9 = blue
A14 = Stop (Keyword #2)
A15 = Yellow

B26 = Go (Keyword #1)
B27 = 40
G24 = Stop (Keyword #2)
G25 = Moo

Here is the end result on a new sheet in the workbook:
A B C D...
Row 1: Green Red
Row 2: blue Yellow
Row 3: 40 Moo