Copy Formula and Output from 2 different excel file

  • Hi Guys,


    I'm trying to create a new Excel Output File that contains the calculation taken from 2 different excel files.


    Basically, I want to output the result of the following:


    Excel File1 (A1) + Excel File 2 (A1) = Output File (A1)
    Excel File1 (A2) + Excel File 2 (A2) = Output File (A2)
    Excel File1 (B1) + Excel File 2 (B1) = Output File (B1
    and so on...


    Appreciate if you have any ideas on how to create a macro for this scenario to call Excel File1 and Enter A1 then Call Excel File2 and Enter A1 then Output in in the new Output file.


    Thanks!

  • Re: Copy Formula and Output from 2 different excel file


    I'd probably just write some formulas, or have the macro write the formulas to do the sum. Easiest way would be to open both file, write a formula like:
    ='[Book1.xls]Sheet 1'!A1+'[Book2.xls]Sheet 1'!A1
    Then close both the source workbooks. Formula in your 3rd workbook will now have full file paths. This is what you need to use either manually, or have the macro write. Then, if desired, do a Copy, Paste Specials - Values.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: Copy Formula and Output from 2 different excel file


    Quote from Luke M;720727

    I'd probably just write some formulas, or have the macro write the formulas to do the sum. Easiest way would be to open both file, write a formula like:
    ='[Book1.xls]Sheet 1'!A1+'[Book2.xls]Sheet 1'!A1
    Then close both the source workbooks. Formula in your 3rd workbook will now have full file paths. This is what you need to use either manually, or have the macro write. Then, if desired, do a Copy, Paste Specials - Values.



    Thank you so much Luke! However, is it possible to have an option that I can enter the Cell #s ranges where I want to copy formula in my new excel file?

  • Re: Copy Formula and Output from 2 different excel file


    You should be able to. Could do something like

    Code
    Dim myForm as String
    Dim myRange as String
    myForm = "='[Book1.xls]Sheet 1'!A1+'[Book2.xls]Sheet 1'!A1"
    
    
    myRange = InputBox("What range do you want formulas in?")
    ThisWorkbook.Worksheets("Sheet1").Range(myRange).Formula = myForm

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: Copy Formula and Output from 2 different excel file


    Hi,


    Just reviving this thread to get some inputs in adding a pop up screen to enter a multiplier or expression in my macro. Basically, the macro below which adds the contents of cell C8 in Book1 and Book2. I need to add a multiplier in cell C8 of Book2 where in the pop screen will let me enter the multiplier or an expression.


    So the new formula will be C8+C8(any number or expression eg. 4/6)


    Any ideas?


    Sub ne()
    Dim myForm As String
    Dim myRange As String
    myForm = "='[Book1.xls]Concentration Table'!C8+'[Book2.xls]Concentration Table'!C8"


    myRange = InputBox("What cells do you want calculations in?")
    ThisWorkbook.Worksheets("Sheet1").Range(myRange).Formula = myForm
    End Sub



    Thanks!

  • Re: Copy Formula and Output from 2 different excel file


    If I understood correctly, I think this is what you want.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: Copy Formula and Output from 2 different excel file


    Thanks for the reply... when I tried the macro it errors in this line myRange.Formula = myForm * myMult

  • Re: Copy Formula and Output from 2 different excel file


    Oops, you're right. Forgot to put quotation marks in. Line should be:

    Code
    myRange.Formula = myForm & "*" &  myMult

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: Copy Formula and Output from 2 different excel file


    Hey guys! Need your help again.


    How do I insert the IFERROR in my formula below? I want to get rid of the #VALUE! in some cells and put a blank space instead?


    TIA!


    Sub ne()
    Dim myForm As String
    Dim myRange As Range
    Dim myMult As Double
    myForm = "='[Book1.xls]Concentration Table'!C8+'[Book2.xls]Concentration Table'!C8"



    'Used Application.InputBox so user can select cells with mouse, rather than typing
    Set myRange = Application.InputBox("What cells do you want calculations in?", "Where to go", , , , , , 8)
    'Used Evaluate so user can type fractions, like 4/6
    myMult = Evaluate(InputBox("What should we multiply Book2's number by?", "Multiplier", 1))
    myRange.Formula = myForm & "*" & myMult
    End Sub

  • Re: Copy Formula and Output from 2 different excel file


    You need to change the myForm variable then.

    Code
    myForm = "=IFERROR('[Book1.xls]Concentration Table'!C8+'[Book2.xls]Concentration Table'!C8,"""",)"

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: Copy Formula and Output from 2 different excel file


    Thanks for your usual help. I tried replacing the myForm but end up getting error on.. myRange.Formula = myForm & "*" & myMult


    Any ideas?



    Quote from Luke M;726395

    You need to change the myForm variable then.

    Code
    myForm = "=IFERROR('[Book1.xls]Concentration Table'!C8+'[Book2.xls]Concentration Table'!C8,"""",)"

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!