Attempting to create macro that finds a value and then transposes that row to a column

[exoscoriae]

Hello all.

I have a spreadsheet with well production data.

Each worksheet is a different month/year, so Jan 2013, Feb 2013, March 2013, etc...

On each page, it is set up something like this:

Well Name #1
	Unimportant Data	51	87	35	12	20	11	0	0
	Total Gas	3	8	2	9	4	4	2	8
	Unimportant Data	6	8	2	8	0	0	0	0
	Water	9	8	2	2	1	93	0	0
Well Name #2
	Unimportant Data	8	42	1008	1	0	0	0	0
	Total Gas	16	387	42	12	7	32	1	0
	Unimportant Data	3	7	9	5	1	11	13	15
	Water	94	44	76	0	0	0	0	0

The idea here is that each well has 17 fields that are tracked in this spreadsheet (my example above shows 3-4). For production purposes however, only 3 are relevant. Currently, an assistant highlights the row (lets pretend DataField3 in this case), and copies it to a new sheet vertically. She then does this for the two other fields for the well. And then repeats this process for every well in the spreadsheet (LOTS OF THEM).

I'd like to automate this for her, and create a process that simply scans through the document, finds the relevant fields, and copies them to a new spreadsheet in a transposed format.

A few identified hangups:
The data being copies isn't very useful unless it also has the name of the well that it related to. The name of the well is in column A, however well names are not the *only* data in column A.
The spacing on the spreadsheet is not static. New lines get added and subtracted, so I have to use some sort of find feature to dynamically locate the rows of interest for each well.
The number of data points in each row is variable, so it has to be able to identify the end point of the rows range.

So while I haven't been able to figure out the actual working code, here is how I imagine it working:

For B1:b421 (the entire range of colum B, and where the data field names reside), find "Total Gas" (this is the first data row in each well that needs to be transposed). Once this cell is located, then I can derive the well name by somehow selecting the cell that is 8 up and 1 to the left. In other words, if "Total Gas" was in cell B32, then the name of the well it correlates with is in cell A24. This offset is identical for every well.

Once I derive the cell the well name is in, I copy this to a new sheet. (lets say, A1).
Then, below that I transpose all of the data in the Total Gas row. For this example if the string "Total gas" was found in cell; B32 (as above), then data for this row would exit in D32:AH32. And the idea would be to transpose all the data that exists in that range vertically below the previously copied Well Name. If the prior well name did in fact get copied to A1, thenthis transposed data would start at A2 for example, and go down vertically.

The macro/code then seeks the next two rows of interest. One called "Corrected Intellisite" and one called "Water". It then transposes this information into columns B and C, starting at B2 and C2 respectively.

At this point it seeks the next instance of "Total Gas", derives the next well name, pastes the well name a few lines below the last used cell from the previous transpose operation, and repeated the process.

The final product is a spreadsheet which has a well name, 3 columns of data below it, a blank row or 2, a well name, 3 columns of data below it, a blank line or 2, etc....

I can see the process in my head, I just don't know how to code it. Any assistance in transforming the idea above into something workable would be *greatly* appreciated.

The simple version of what I am looking for is a macro that identifies 3 repeating text strings and transposes the set of values on the same row vertically on a new sheet. Each set it titled by a text string that exists at an offset of 8 cells up and 1 ell to the left of the first data sets string.

[alansidman]

this would be easier to understand with a sample workbook.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[exoscoriae]

I have created a sample worksheet with a before/after worksheet and attached it. To simulate the real worksheet I have left in lots of broken formulas that would generally be pointing to other worksheets.

Each month, a new worksheet is created. So the macro would ideally be able to either run on whatever the active worksheet is, or provide some sort of prompt to select which one. At which point it would then ideally create a new worksheet, and paste all the transposed values into it in a similar format to the worksheet named "After" in the sample.