"Dynamic Excel Formula for Transposing Data Across Rows in Different Sheets"

[HansV]

How about this?

Code: Select all

Sub CreateFormulas()
    Dim ws As Worksheet
    Dim wt As Worksheet
    Dim r As Long
    Dim m As Long
    Dim n As Long
    n = Worksheets.Count
    Set ws = Worksheets(1)
    m = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    For r = 3 To m
        If r > n + 1 Then Exit For
        Set wt = Worksheets(r - 1)
        wt.Range("B2").Resize(7).FormulaArray = "=TRANSPOSE(" & ws.Name & "!B" & r & ":H" & r & ")"
    Next r
End Sub

[vaxo]

Thanks, it works.