the code is perfect for the case cell is B1 but try to change different cells A1, F1, G1 the output will be different and the formula has to be changed manually each time.
I tried to make it as public procedure but I couldn't adjust the cell output
Code: Select all
Sub RepeatHeaders(ByVal N As Long, ByVal headerText, ByVal outputCell As Range)
Dim a, s As String, l As Long
l = UBound(headerText) + 1
s = "{""" & Join(headerText, """,""") & """}"
With outputCell.Resize(, N * l)
.FormulaArray = "=INDEX(" & s & ", MOD(COLUMN(" & .Address & ")-" & outputCell.Column - 1 & "," & l & "))"
.Value = .Value
End With
End Sub