Transpose formulas in excel

You could try this

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

Or copy and paste special - transpose?

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

Select allOpen in new window

See attached.

Dave
Transpose-Formulas-r1.xlsm

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

Select allOpen in new window

Sorry ignore mine, had overlooked your formulae.

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

Perfect!

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

Select allOpen in new window