hello,

I'm new to VBA, so I would very much appreciate any hints considering the following problem.

I am trying to create a macro, which copies the sample sheet "Example, Ben", renames it and adds a hyperlink to it to the Overview sheet. This far it works. However, also some selected columns from the new sheet have to be linked to the Overview sheet, so I need to create a reference to transposed entries from the new sheet. And this is where I get the

"Run-time error '1004': Unable to set the FormulaArray property of the Range class"

here's the code:

Dim NewSheet As String
NewSheet = Application.InputBox("...")
Sheets("Example, Ben").Select
Sheets("Example, Ben").Copy Before:=Sheets("Contracts")
ActiveSheet.Name = NewSheet
Sheets("Overview").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=Chr$(39) & NewSheet & Chr$(39) & "!A1", TextToDisplay:=NewSheet
ActiveCell.Offset(0, 3).Select
ActiveCell.Resize(, 52).Select
Selection.FormulaArray = "=TRANSPOSE(" & NewSheet & " !R[-16]C[5]:R[35]C[5])"

Thanks a lot in advance!!