[RESOLVED] Exporting an array formula

[MartinLiss]

I have a worksheet that has an array formula that begins with
=IF(_xlfn._xlws.FILTER('Support - TAM'!H:L,('Support - TAM'!L:L="Complete")

When I copy that sheet to another workbook it shows up with the name of my source workbook like this. How do I prevent that?
=IF(_xlfn._xlws.FILTER('[Test.xlsm]Support - TAM'!H:L,('[Test.xlsm]Support - TAM'!L:L="Complete")

[Zvoni]

Have you tried hitting F2 (Edit-Mode) before Copy/Pasting?

[MartinLiss]

The copying of the worksheets is being done by VBA code.

[MartinLiss]

I changed the formulas to non-array formulas and the problem went away.

[Dan_W]

Alternatively, you could use:

TargetWS.UsedRange.Formula = SourceWS.UsedRange.Formula

By way of (unnecessarily long) example:

Sub TransferFormulas()
    
    Dim SourceWS As Worksheet, TargetWS As Worksheet
    Dim SourceWB As Workbook, TargetWB As Workbook
    Dim TempState As Boolean
    
    Set SourceWB = Application.Workbooks("SourceWorkbook.xlsx")
    Set TargetWB = Application.Workbooks("TargetWorkbook.xlsx")
    
    Set SourceWS = SourceWB.Sheets("CopyMe")
    SourceWS.Copy After:=TargetWB.Sheets(TargetWB.Sheets.Count)
    Set TargetWS = TargetWB.Sheets(TargetWB.Sheets.Count)
    
    TempState = Application.DisplayAlerts
    Application.DisplayAlerts = False
    TargetWS.UsedRange.Formula = SourceWS.UsedRange.Formula
    Application.DisplayAlerts = TempState
    
End Sub