How to export data with functions to another workbook in excel

0 votes

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.

enter image description hereimage

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
Oct 30 in Others by Kithuzzz
• 20,660 points
36 views

1 answer to this question.

0 votes
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.
answered Oct 30 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
0 answers
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24 in Others by narikkadan
• 37,660 points
117 views
0 votes
1 answer

How to create a drop-down in excel with custom values

You can accomplish that using code rather ...READ MORE

answered Sep 25 in Others by narikkadan
• 37,660 points
64 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17 in Others by Edureka
• 13,640 points
106 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3 in Others by narikkadan
• 37,660 points
89 views
0 votes
1 answer

Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

answered Oct 3 in Others by narikkadan
• 37,660 points
68 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7 in Others by narikkadan
• 37,660 points
87 views
0 votes
1 answer

How can I sort one set of data to match another set of data in Excel?

In addition, INDEX MATCH is a more ...READ MORE

answered Oct 29 in Others by narikkadan
• 37,660 points
43 views
0 votes
1 answer

Unable to import data in excel from another website using VB code

Replace : Set ieTable = ieDoc.all.Item("report-table") With: Set ieTable = ...READ MORE

answered Sep 21 in Others by narikkadan
• 37,660 points
70 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP