VBA: transpose function across multiple worksheets

[nickharg]

I was wondering if anyone knew how to use VBA to solve the following problem -

I am transposing several ranges of data from Sheet2 to Sheet8-Sheet57. A detailed description is as follows:

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

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

I need to do this a total of 12 times for each worksheet - I continue the above pattern out to transposing the horizontal range B608:X608 in Sheet2 to the vertical range W7:W29 in Sheet8. {=transpose('Sheet2'!B608:X608)}.

--------------------------------------------------------

Once I'm done transposing to Sheet8 I can move on to transposing from Sheet2 to Sheet 9. The overall pattern is the same, but each horizontal range from Sheet2 is one row lower - as follows:

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

and on and on until we finish the 12 iteration by transposing the horizontal range of B609:X609 in Sheet2 to the vertical range W7:W29 in Sheet9. {=transpose('Sheet2'!B609:X609)}.

---------------------------------------------------------

By carrying out the patterns described above, the functions for Sheet57 (the last one) as as follows:

Horizontal range B52:X52 of values to vertical range L7:L29 in Sheet57 {=transpose('Sheet2'!B53:X53)}
Horizontal range B107:X107 of values to vertical range M7:M29 in Sheet57 {=transpose('Sheet2'!B107:X107)}
Horizontal range B162:X162 of values to vertical range N7:N29 in Sheet57 {=transpose('Sheet2'!B162:X162)}

The final function would transpose the horizontal range of B657:X657 in Sheet2 to the vertical range of W7:W29 in Sheet57. {=transpose('Sheet2'!B657:X657)}

---------------------------------------------------------

Disclaimer: All the Sheet names listed in this thread only refer to the actual location of the specific worksheet. 'Sheet2' is really named 'Invoiced'. Sheet8:Sheet57 are currently named Client1:Client50 but their names automatically update with the Range A3:A52 in 'Sheet2' (AKA 'Invoiced') thanks to a nifty piece of code someone from this discussion board wrote for me.

Any help of this would be greatly appreciated. I know this was extremely long winded but I wanted to be as clear as possible.

-Thanks!

 

[nickharg]

Finally figured it out. I had to go into your code and change:
"=transpose(sheet5!B" & A + ShtCtr & ":X" & A + ShtCtr & ")" to:
"=transpose('labor distribution'!B" & A + ShtCtr & ":X" & A + ShtCtr & ")".

Can't believe I didn't see this earlier; sorry for asking so much more out of you than was required. Thanks a million for all you've done.

 

[Tinbendr]

WooYoo!!!!!!

That's great!

Enjoy