We help IT Professionals succeed at work.

Transpose formulas in excel

biker9
biker9 asked
on
Hello experts,
I need a macro to transpose formulas found in sheet 1 to sheet 2 as in the attached sample.
Thanks,
biker9 Transpose-Formulas.xlsx
Comment
Watch Question

You could try this

=INDEX(Sheet1!$F$9:$F$26,COLUMNS($F$15:F15))

Or copy and paste special - transpose?
Most Valuable Expert 2012
Top Expert 2012

Commented:
Here's your macro:

 
Sub transposeFormulas()
Dim wks As Worksheet
Dim outWks As Worksheet
Dim outCursor As Range, inCursor As Range

    Set wks = ThisWorkbook.Sheets("Sheet1")
    Set outWks = ThisWorkbook.Sheets("Sheet2")
    
    Set inCursor = wks.Range("F9:F26")
    Set outCursor = outWks.Range("F16")
    
    inCursor.Copy
    outCursor.PasteSpecial Paste:=xlPasteFormulas, Transpose:=True

End Sub

Open in new window


See attached.

Dave
Transpose-Formulas-r1.xlsm
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
If you wanted to generalize the selection of source and destination cells, you might try the following macro. First select the source cells, then run the macro. It will ask you to click on the first destination cell.

Sub FormulaTransposer()
    Dim cel As Range, rgCopy As Range
    Set rgCopy = Selection
    On Error Resume Next
    Set cel = Application.InputBox("Pick the first cell where transposed formulas get pasted.", Type:=8)
    On Error GoTo 0
    If Not cel Is Nothing Then
        rgCopy.Copy
        cel.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    End If
End Sub

Open in new window

Sorry ignore mine, had overlooked your formulae.

Author

Commented:
That's perfect, and will be very useful, however after running the code, I realized I actually only needed the cell references changed rather than the formula, as in the attached sheet. Should I re-post a separate question (I will award points for your original solution), or is it a simple tweak to get this to work?
Thanks,
biker9
Transpose-references.xlsx
Most Valuable Expert 2012
Top Expert 2012
Commented:
No need.

Here's the code:
 
Sub transposeFormulas()
Dim wks As Worksheet
Dim outWks As Worksheet
Dim outCursor As Range, inCursor As Range

    Set wks = ThisWorkbook.Sheets("Sheet1")
    Set outWks = ThisWorkbook.Sheets("Sheet2")
    
    Set inCursor = wks.Range("F9:F26")
    Set outCursor = outWks.Range("F16")
    
    For Each mycell In inCursor
        outCursor.Formula = "='" & mycell.Worksheet.Name & "'!" & mycell.Address
        Set outCursor = outCursor.Offset(0, 1)
    Next mycell
    
End Sub

Open in new window


See attached.

Dave
Transpose-Formulas-r2.xlsm

Author

Commented:
Perfect!
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
You can use the TRANSPOSE function to return the values in the target worksheet:
1) Select the target range
2) Array enter the following formula:
=TRANSPOSE(Sheet1!F9:F26)

Macro to automate (and generalize) the above approach:
Sub FormulaReferenceTransposer()
    Dim cel As Range, rgCopy As Range
    Set rgCopy = Selection
    On Error Resume Next
    Set cel = Application.InputBox("Pick the first cell where transposed references go.", Type:=8)
    On Error GoTo 0
    If Not cel Is Nothing Then _
        cel.Resize(rgCopy.Columns.Count, rgCopy.Rows.Count).FormulaArray = "=TRANSPOSE(" & rgCopy.Address(RowAbsolute:=False, ColumnAbsolute:=False, External:=True) & ")"
End Sub

Open in new window

Explore More ContentExplore courses, solutions, and other research materials related to this topic.