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!

 

[Tinbendr]

I didn't do much checking, but see if this will work for you.

Sub MoveemRound()

Dim Counter As Long
Dim A As Long
Dim B As Long
Dim ShtCtr As Long

    For B = 8 To 57
        ShtCtr = B - 8
        Counter = 11 'one less
        For A = 3 To 55 * 12 Step 55
            Sheet2.Range("B" & A + ShtCtr & ":X" & A + ShtCtr).Copy
            Counter = Counter + 1
            Worksheets(B).Cells(7, Counter).PasteSpecial Paste:=xlPasteAll, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        Next
    Next
    Application.CutCopyMode = False
End Sub

 

[nickharg]

This is very close, but not exactly what I need in this situation. I am looking to use a =transpose function so that when I update the cells in 'Invoiced' (Sheet2), the horizontal ranges in Sheets8-57 will automatically update. Your code takes the data in Sheet2 and copies it to Sheets8-57, meaning that every time another cell in Sheet2 is filled in the macro will need to be run again.

The code also copies some random formatting (cell borders and such) into Sheets8-57. Is there any way to fix these issues?

Regardless, I much appreciate your help.

 

[Tinbendr]

I think I've got it this time.

Sub MoveemRound2()
Dim Counter As Integer
Dim A As Long
Dim B As Long
Dim ShtCtr As Long
    For B = 8 To 57
    With Worksheets(B)
        ShtCtr = B - 8
        Counter = 11
        For A = 3 To 55 * 12 Step 55
            Counter = Counter + 1
            'Clear contents.  It will error if you try to rewrite formula without clearing.
            .Range(.Cells(7, Counter + ShtCtr), .Cells(29, Counter + ShtCtr)).Clear
            .Range(.Cells(7, Counter + ShtCtr), .Cells(29, Counter + ShtCtr)).FormulaArray = _
                "=transpose(sheet2!B" & A & ":X" & A & ")"
        Next
    End With
    Next
 
End Sub

 

[nickharg]

Ahh so close. You're going to hate me but I just realized I'm actually not transposing from Sheet2 but from Sheet5 ('Labor Distribution') instead. I tried changing up your code to match this, but doing so causes it to break. I double checked my original post and this detail was the only thing I messed up, although I'm sure it had a great affect on your much appreciated work.

Again, I apologize; however, if they only change this would require is to change:
"=transpose(sheet2!B" & A & ":X" & A & ")"
from your code to read:
"=transpose(sheet5!B" & A & ":X" & A & ")"
it would seem that a solution has yet to be identified.

Thank you so much for all your help so far. I posted this same question a little over a week ago and struck out completely - everything you've done so far is more than I could ask for.

-Thanks

 

[Tinbendr]

but doing so causes it to break.

How so?

It shouldn't make any difference to the code, just to the formula. Is the formula then wrong?

 

[nickharg]

The only alteration I made to your code was to switch it to Sheet5 and remove the line:

'Clear contents. It will error if you try to rewrite formula without clearing.

I cleared all contents in the source cells from Sheet5 and the destination cells from Sheets8-57.

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

When I run the macro Excel seems as if it is trying to open a file. A file browser pops up with "Update Values: sheet5" at the top of the window. Every time I push cancel one row after the next is filled in by the macro. I went through and pushed cancel enough times for all 50 worksheets to be filled in and this is what is observed:

Sheet8 seems to be fine, the formula in cell L7 is {=transpose(sheet5!B3:X3)} and the formula in cell X7 is {=transpose(sheet5!B608:X608)}. Although something isn't quite right because these formulas yield a #REF error.

As we move over to Sheet9 the formulas stay the same (also incorrect, see next paragraph), but everything has been shifted over one column - there is nothing in column L, instead the transpose functions go from M:X. This pattern is continued onward in the same manner so that in Sheet57 the transpose functions are located in columns BI:BT.

The transpose formulas from Sheet8-9 and onward should not all be the same. Sheet8 should range from {=transpose(sheet5!B3:X3)} in L7 to {=transpose(sheet5!B609:X609)} in W7, but Sheet9 should range from {=transpose(sheet5!B4:X4)} in L7 to {=transpose(sheet5!B609:X609)} in W7 (transposing a range one row lower than those pertaining to Sheet8). This pattern continues out to Sheet57 ranging from {=transpose(sheet5!B52:X52)} in L7 to {=transpose(sheet5!B657:X657)} in W7.

 

[Tinbendr]

How are you populating sheet5? Sounds like a query is interfering with the references.

I had my offset counter (ShtCtr) in the wrong place, too. I got all turned around when I had to flip the code.

Sub MoveemRound3()
Dim Counter As Integer
Dim A As Long
Dim B As Long
Dim ShtCtr As Long
    For B = 8 To 57
    With Worksheets(B)
        ShtCtr = B - 8
        Counter = 11
        For A = 3 To 55 * 12 Step 55
            Counter = Counter + 1
            'Clear contents.  It will error if you try to rewrite formula without clearing.
            '.Range(.Cells(7, Counter), .Cells(29, Counter)).Clear
            .Range(.Cells(7, Counter), .Cells(29, Counter)).FormulaArray = _
                "=transpose(sheet5!B" & A + ShtCtr & ":X" & A + ShtCtr & ")"
        Next
    End With
    Next
End Sub

This is how I'm populating my sheet5.

Sub rnddata()
Randomize

For A = 2 To 24
For B = 3 To 12 * 55

Sheet5.Cells(B, A) = Int(99 * Rnd) + 1

Next
Next
End Sub

And clearing sheet 8-57

Sub Clearall()

For A = 8 To 57
Worksheets(A).UsedRange.ClearContents
Next

End Sub

 

[nickharg]

Is it necessary to clear all the cells on Sheets8-57? When I run your clearall macro it clears the contents of all the cells, not just the ones that need to be filled in by the transpose functions.

When I run rnddata an error message pops up saying that I 'cannot change part of an array' (Run-time error 1004)

If I run your latest MoveemRound3 - again I have to press cancel on an open file dialogue box 600 times, but the results still come back as #REF. The formulas are, however, in the right locations on Sheets8-57 and successfully follow the pattern of transposing one row lower from Sheet 5 for every new sheet.

 

[Tinbendr]

I found that I couldn't paste a NEW formula into a cell that already had an array formula. That's why I cleared the range of the sheet with exactly same dimensions before inserting the formula again.

I assume that this code is just to setup all the formulas? And that after running it once, you won't need it again? If so, you may have to start with a fresh workbook, run the code, and add you data to it.

The code is working on my sample sheet. The two short routines I included was just demonstrating how I set it up and how I cleared it.

Is there another array formula somewhere on the sheets that cross over the formula we're trying to insert? (I'm just grabbing at straws now. Without the original, I can't troubleshoot it.)