Challenging Excel Macro Help

Hi,
I posted this in another forum but thought I might have a little more luck in this one.

I'm trying to transpose up to 400 rows of data to columns. I'm stuck though, as this is way out of my league.

Perhaps the best way to show what I'm trying to accomplish is in the attached (zipped) spreadsheet. The attached Excel workbook has a demo of what my source data would look like, and then what I want the data to look like after the Macro does its job.

This is complicated, I'm afraid, so I will go into more detail:

Step 1: From the "source" sheet, I'd like the cell range (A2 to I2) copied into the first 25 rows of the "after" sheet. So the data in (A2 to I2) from the source sheet will be pasted into the cell range from (A2 to I2) to (A26 to I26) of the after sheet. Then, the same thing for the second set of data. Data from source sheet cells (A6 to I6) pasted to the cell range on the after sheet from (A27 to I27) to (A51 to I51) the source sheet copied to the next 25 rows of the after sheet, and so on. I'd like this to occur for up to 400 sets of this type of data on the source sheet.

Step 2: Similar functionality to Step 1. Take source sheet data from cells (AJ2 to AQ2) and paste into the range of after sheet cells from (P2 to W2) to (P26 to W26). Repeat for source cells (AJ6 to AQ6), pasting that data into the after sheet cell range from (P27 to W27) to (P51 to W51). Repeat, again, for up to 400 sets of this type of data from the source sheet.

Step 3: Transpose source sheet cells (K1 to AI1) to after sheet cells (J2 to J26). Repeat this for the next set of data, so transpose source sheet cells (K5 to AI5) to after sheet cells (J27 to J51). Repeat, again, for up to 400 sets of this type of data from the source sheet.

Step 4: Similar functionality to Step 3. Transpose source sheet cells (K2 to AI2) to after sheet cells (K2 to K26). Repeat this for the next set of data, so transpose source sheet cells (K6 to AI6) to after sheet cells (K27 to K51). Repeat, again, for up to 400 sets of this type of data from the source sheet.

Step 5: Similar functionality again. Transpose source sheet cells (K3 to AI3) to after sheet cells (L2 to L26). Repeat this for the next set of data, so transpose source sheet cells (K7 to AI7) to after sheet cells (L27 to L51). Repeat, again, for up to 400 sets of this type of data from the source sheet.

Step 6: Similar functionality yet again. Transpose source sheet cells (K4 to AI4) to after sheet cells (M2 to M26). Repeat this for the next set of data, so transpose source sheet cells (K8 to AI8) to after sheet cells (M27 to M51). Repeat, again, for up to 400 sets of this type of data from the source sheet.

That's it! Maybe I'm making this harder than it is... but can anyone out there please help me?

Thanks!

Will each section always be 3 lines?

I will assume that the format doesn't change at all. Here is the code that I would build, you might want to add error handling to it and improve the structure though...

Sub test_mac()

Dim sec1_array()
Dim sec2_array()
ReDim sec1_array(9)
ReDim sec2_array(8)
Dim c1 As String
Dim c2 As String


Sheets("demo_source_data_sheet").Activate

n = 2
w = 2

Do Until Cells(n, 1) = ""
Sheets("demo_source_data_sheet").Activate
sec1_array = Range(Cells(n, 1), Cells(n, 9))
sec2_array = Range(Cells(n, 36), Cells(n, 43))
c1 = Right(Cells(n + 1, 9), Len(Cells(n + 1, 9)) - 4)
c2 = Right(Cells(n + 2, 9), Len(Cells(n + 2, 9)) - 4)
Range(Cells(n - 1, 11), Cells(n + 2, 35)).Copy
Sheets("demo_sheet_after_macro_conversn").Activate
Cells(w, 10).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Range(Cells(w, 1), Cells(w + 24, 9)) = sec1_array
Range(Cells(w, 16), Cells(w + 24, 23)) = sec2_array
Cells(w, 14) = c1
Cells(w, 15) = c2
n = n + 4
w = w + 25
Sheets("demo_source_data_sheet").Activate
Loop

Application.CutCopyMode = False


End Sub

Thanks SO MUCH, Mike!

But it's getting stuck on declaring the variable at the line:

c1 = Right(Cells(n + 1, 9), Len(Cells(n + 1, 9)) - 4)

Looks like it coud work though. What do you think?

This might be where you need error handling. If the cell is blank or less than 4 characters in length an error will pop up. Perhaps adding an If len(cells(n,x)) > 4 then would fix it.