Macro for Transpose Function?

[nickharg]

Alright so this is pretty hard to describe, but I'm hoping someone could help me out.

I've got a massive workbook going with over 50 sheets (trust me, they're all necessary). I am currently in the process of manually putting in hundreds of transpose functions and I was wondering if there was a macro or something I could use to help me out.

An example of what I am transposing in a given worksheet:

Horizontal range B3:X3 of values to vertical range L7:L29 in Sheet2 {=transpose('Sheet1'!B3:X3)}
Horizontal range B58:X58 of values to vertical range M7:M29 in Sheet2 {=transpose('Sheet1'!B58:X58)}
Horizontal range B113:X113 of values to vertical range N7:N29 in Sheet2 {=transpose('Sheet1'!B113:X113)}

As you can see, with each new function I need to move down 55 rows in Sheet1 and over 1 column in Sheet2.

I need to do this a total of 12 times for each worksheet. So...once I continue the above pattern out to {=transpose('Sheet1'!B608:X608)} for a total of 12 columns in Sheet2, the last of which being W7:29, I can finally move on to the next worksheet (transposing Sheet1 data to Sheet3).

This action is essentially the same, but the desired data in Sheet1 is one row lower:

Horizontal range B4:X4 of values to vertical range L7:L29 in Sheet3 {=transpose('Sheet1'!B4:X4)}
Horizontal range B59:X59 of values to vertical range M7:M29 in Sheet3 {=transpose('Sheet1'!B59:X59)}
Horizontal range B114:X114 of values to vertical range N7:N29 in Sheet3 {=transpose('Sheet1'!B114:X114)}

continuing all the way to:
Horizontal range B609:X609 of values to vertical range W7:W29 in Sheet3 {=transpose('Sheet1'!B114:X114)}

Now, I know this is very complicated (at least to me), but I described it to the best of my ability. Does anyone know of ANY way that I could save some time in doing this? So far I've transposed the data from Sheet1 to Sheets2-8 and, if I did the math right, to finish the rest of my workbook in this manner would require another 516 transpose functions...no fun.

Maybe a macro? I don't know, just shooting this out into the dark.

-Thanks

 

[JLGWhiz]

This will make it a little easier, but it only handles two sheets at a time. It could easily be adapted to do all fifty sheets if the same rows and columns would be affected. The sheet numbers will have to be changed on the Set lines in this one each time you change sheets. And if the rows and columns are different, they will also need to be adjusted.

Code:
Sub xpose()
Dim sh As Worksheet, rng As Range, lr As Long, sh2 As Worksheet
Set sh = Sheets(1)
Set sh2 = Sheets(2)
Set rng = sh.Range("B3:B608")
col = 12
For i = 3 To 608 Step 55
sh.Range("B" & i).Resize(1, 23).Copy
sh2.Cells(7, col).PasteSpecial Paste:=xlPasteAll, Transpose:=True
col = col + 1
Next
End Sub
Code:

 

[nickharg]

Thank you so much, I'll try it as soon as I get in to the office tomorrow and reply with an update.

 

[JLGWhiz]

If you are repeating the same process, i.e. copying the same rows from sheet1 and posting to sheets 2 thru 50 in the same columns then this might be a better procedure for you. It will walk through all the sheets instead of one at a time. If you have already completed some sheets you can change this line:

If ws.Index <> 1 Then

To something like this:

If ws.Index > 2 'or whatever the last sheet index number you completed.

The index numbers are the same order left to right as the tabs at the bottom of your screen, no matter what the tabs read.

Code:
Sub xpose2()
Dim sh As Worksheet, ws As Worksheet
Set sh = Sheets(1)
Set rng = sh.Range("B3:B608")
col = 12
For Each ws In ThisWorkbook.Sheets
If ws.Index <> 1 Then
MsgBox ws.Index
col = 7
For i = 3 To 608 Step 55
sh.Range("B" & i).Resize(1, 23).Copy
ws.Cells(7, col).PasteSpecial Paste:=xlPasteAll, Transpose:=True
col = col + 1
Next
End If
Next
End Sub
Code:

 

[nickharg]

I'm transposing to the same columns in every sheet 2+, but the cells I'm transposing from in Sheet1 are shifted down 1 row for every new sheet.

Example:

To fill in the first column of Sheet2 I would use:
Horizontal range B3:X3 of values to vertical range L7:L29 in Sheet2 {=transpose('Sheet1'!B3:X3)}
To fill in the second column of Sheet2 I would use:
Horizontal range B58:X58 of values to vertical range M7:M29 in Sheet2 {=transpose('Sheet1'!B58:X58)}

To fill in the first column of Sheet3 I would use:
Horizontal range B4:X4 of values to vertical range L7:L29 in Sheet3 {=transpose('Sheet1'!B4:X4)}
To fill in the second column of Sheet3 I would use:
Horizontal range B59:X59 of values to vertical range M7:M29 in Sheet3 {=transpose('Sheet1'!B59:X59)}

...and on and on through 12 iterations.