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

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

It worked great Luke thank you so much!

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

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

Thank you so much! will test the results now.. appreciate your help

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", , , , , ,
'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

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,"""",)"