Duplicate line and transpose data

If I do this manually, it’s easy to identify because I can see if the worksheet actually has the 4 quarterly columns in it. But the process is tedious when there are many data rows. If there are 100 lines, I need to repeat the same process 100 times. I want to build a macro to break each line with 4 quarterly score into four separate lines. The macro can change the data directly on the same worksheet, no need to create new worksheet.

Here are the steps.

1. Find 4 quarterly average, however, the heading texts are not consistent. These are the heading I can identify so far: Qtr1, Q01, Q’01, Q1’2014 , Q1’14.
2. Note: To make it even more difficult, there may be other column in between “Teacher”, “School”, “Class Number”, “Location”, “Average”, but the Q1 to Q4 column will almost always be continuous sequence (no other column in between them).
3. If the macro can’t find the 4 columns (qtr1 to qty4) with average data, then exit sub.
4. If found, do this.
   • Insert a new column, after “Location”, call it “C1”.
   • Insert a new column, call it “Qtr Avg”, at the last column .
   • Go to first teacher (first data row), copy the line.
   • Duplicate it 3 times, insert the lines below the line it copied.
   • In “C1” column, get the value from column “Class Number”, then add “-R-Q0#”. # is the quarterly number from 1 to 4.
   • In “Qtr Avg” column for each of the 4 lines, get/copy the respective quarter average. Then round up the value with no decimal point e.g. round(average, 0).
   • Go to next teacher. Repeat steps C to G.
   • Once the last data row is done, we can remove these 4 columns “Average”, “Q1”, “Q2”, “Q3” and “Q4”.