How to export data with functions to another workbook in excel

Question

Please refer to the image below. I need to save the data from column "Total Qty" in a certain folder with the file name "Shipment ID" and copy it to another workbook. Here, I think back to that file and get data from it for a different column.

I need to add one more item to this, namely, I want to confirm whether the file is present in the particular folder or not. Update the file if it already exists. Create a file using the "Total Qty" field if the file doesn't already exist.

For this, I created the code listed below. It worked up till I set the column "total qty "functions to "=(G2+H2)". The destination file displays "=(#REF!+#REF!)" in cells after the function has been entered.

Sub Export_Total_Qty()

    Application.ScreenUpdating = False
    Dim FilePath As String
    Dim ID As String, abc

    Const FILE_INFO As String = "D:\Excel Software\Shipment Tracking\Junk\<id>.xlsx"

    ID = Worksheets("Sheet1").Cells(1, "O").Value

    abc = Replace(FILE_INFO, "<id>", ID)

    FilePath = ""
    On Error Resume Next
    FilePath = Dir(abc)
    On Error GoTo 0
    If FilePath = "" Then


         Sheets("Sheet1").Range("I2:I50").Copy
         Workbooks.Add
         ActiveSheet.Paste Destination:=Range("A1")
         ActiveWorkbook.SaveAs _
        Filename:=abc

         MsgBox "New Entry is created, Thak you", vbInformation, "Kutools for Excel"
    Else
        Sheets("Sheet1").Range("G1:G50").Copy
        ActiveSheet.Paste Destination:=Range("A1")
        'ActiveWorkbook.Update _
        Filename:=abc
        MsgBox "successfully Updated", vbInformation, "Kutools for Excel"
    End If
    Application.ScreenUpdating = False


End Sub

Answer 1

The issue appears to be that once you leave the context that those formulas are pointing to, your reference becomes unreliable (columns in your source worksheet become bad references). Use the Paste Special function to declare that you only want to copy the values if you want to try this. Change ActiveSheet to achieve that. Into ActiveSheet, paste Destination:=Range("A1"). Range("A1"). xlPasteValues PasteSpecial Operation.

I hope this helps you.