VBA Saving formulas to non-macro enabled sheet without original sheet reference

I have a macro workbook that adds formulas to a sheet (along with other data).

I want to transfer the sheet with the resulting formulas and data to xlsx version.

I tried creating a new xlsx sheet and copy / pasting the sheets I wanted to the new workbook using VBA. Unfortunately the formulas in the new workbook referenced the original workbook.

What is the best method to transfer the data and formulas to a new workbook, without having them reference the original work?

An example of the issue within the formulas:

Formula in original xlsm sheet:

=SUMIFS('raw-data'!S:S,'raw-data'!B:B,'price-compare'!B5,'raw-data'!M:M,'price-compare'!F5,'raw-data'!O:O,'price-compare'!J3)

Formula in new xlsx sheet

=SUMIFS('[billing-compare.v.1.0.xlsm]raw-data'!S:S,'[billing-compare.v.1.0.xlsm]raw-data'!B:B,'price-compare'!B6,'[billing-compare.v.1.0.xlsm]raw-data'!M:M,'price-compare'!F6,'[billing-compare.v.1.0.xlsm]raw-data'!O:O,'price-compare'!J3)

As you can see the formulas in the copied sheet contain reference to the original sheet in a different workbook.

I need prevent the reference back to the originating workbook.

Thanks.

A way to copy formulas from one workbook to another, without having that [ is something like this:

Sub CopyFormulas()
    
    Dim wkb As Workbook
    Set wkb = Workbooks.Open(ThisWorkbook.Path & "\" & "Source.xlsx")
    
    Dim wksName As String
    wksName = "Sheet1"
    
    Dim myFormulaCells As Range
    Set myFormulaCells = GetFormulaCells(wkb.Worksheets(wksName))
    
    If myFormulaCells Is Nothing Then
        Debug.Print "No formulas present in "; wkb.Name; "->"; wksName
    Else
        Dim myCell As Range
        For Each myCell In myFormulaCells.Cells
            Debug.Print myCell.Address
            Debug.Print myCell.Formula
            
            With ThisWorkbook.Worksheets(wksName)
                .Cells(myCell.Row, myCell.Column).Formula = myCell.Formula
            End With
        Next
    End If
    wkb.Close False
    
End Sub

This is the GetFormulaCells(), which returns the cells with the formula, if these exist:

Function GetFormulaCells(wks As Worksheet) As Range
    
    On Error GoTo FormulaCells_Error:
    
    Set GetFormulaCells = wks.Cells.SpecialCells(xlCellTypeFormulas, 23)
        
    On Error GoTo 0
    Exit Function
    
FormulaCells_Error:
    Err.Clear
    Set GetFormulaCells = Nothing
    
End Function

Assumptions:

• both worksheets Target (the Excel with the VBA code) and Source.xlsx are in the same workbook;
• Sheet1 is the only sheet, in which we are interested to migrate the formulas from and to. It exists in both workbooks;
• You can delete the useless Debug.Print myCell.Address and others yourself;
• In the Target worksheet, the formulas will be copied to the cell addresses that the formulas were having in the Source.Sheet1 worksheet;
• The scenario when there are formulas referring a third workbook =SUM('[another_workbook.xlsx]'!A:A in the source workbook is not tested;