Macro for two excel workbooks

[Brijesh]

Hi All

I have two workbooks "FileA" and "FileB". I have input variable "Premium" in FileA which results in calculation of "Payment Amount" (Cell D10 in FileA) and then this Payment Amount is to be put in Cell A1 of FileB. When Payment Amount is entered in FileB a variable "Income" (Cell B2) is calculated itself in FileB. Now this Income is again to be put in FileA in Cell C3.

I have to write a macro which should pick value of Payment Amount (Cell D10) from FileA and put it in (Cell A1) of FileB. Again it should pick calculated value of Income from FileB and put it in FileA.

Plz suggest the coding.

Thanks in advance

Brijesh

 

[SirJB7]

Hi, Brijesh!
Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.
Regards!

 

[Brijesh]

Hi SirJB

I have uploaded both files. What I want macro to do is as follows:

On Input sheet of FileA there is a Parameter "Premium". On Calc sheet of FileA there is a variable "Corpus". On the same sheet there are given Annuity Factors for Term 1 to 10. For a particular term Corpus = Premium*Annuity Factor (Annuity factor is to be picked for that particular Term). After calculating Corpus in FileA for a particular Term, the same Corpus is to be put on In&Out sheet of FileB. When Corpus is put in FileB, variable Income is calculated (by excel itself, not by macro) on the sheet In&Out for that particular Term. Now this Income is to be put against that particular Term on sheet In&Out in FileA. This whole process is to be repeated for Term 1 to 10 one by one.

The main difficulty I am facing in writing the macro is to write code for switching between two files.

Plz suggest

Further what I think I have to provide file name and file path of FileB somewhere in coding to do so. Is it possible to provide the file name and file path of FileB somewhere in any cell of FileA and the macro code picks the same from the cells in FileA as and when required.

 

[SirJB7]

Hi, Brijesh!

Let us go by parts, as Jack the ripper said.

First of all, the point of how to link one book to the other. Try putting this formula in workbook FileB.xlsx at worksheet In&Out at cell C6:
=[FileA.xlsx]Calc!$H$2

That should do the job without any VBA code involved.

Now the issue of varying "something" for each term from 1 to 10. You can in workbook FileA.xlsm (changing the file type to allow macros) use a simple macro like this:

Option Explicit

Sub VaryingSomething()
    Dim I As Integer
    For I = 1 To 10
        ' do something
    Next I
End Sub

The problem is to define that "something" which I don't have fully clear after giving a look at your files. Could you please elaborate?

Regards!

 

[Brijesh]

Thanks SirJB

But this is only a part of my problem and I was having no problem in this part of coding. Let me take an example.

Premium is given on In&Out sheet of FileA as 12000.
Now suppose I have taken Term of 1 Yr. On the Calc sheet of FileA the annuity factor for Term 1 year is 1. So, for Term 1Yr, Corpus = Premium*Annuity Factor = 12000*1 =12000
Now this Corpus (12000) is to be put on In&Out sheet of FileB in cell C6 (U have given formula "=[FileA.xlsx]Calc!$H$2"). As soon as we put this value in Cell C6 on sheet In&Out of FileB, Income of
0.129505 will appear automatically in cell C12 on sheet In&Out of FileB. Now this Income (0.129505) is to be put against Term 1Yr on sheet In&Out of FileA (i.e. cell C9 on sheet In&Out of FileA is to be allotted value 0.129505).

This whole process is to be repeated for Term 1 to 10.

I have problem in writing down code for this switching between excel files.

Further I cannot assign formula "=[FileA.xlsx]Calc!$H$2" to cell C6 on sheet In&Out of FileB due to some reason. So this work is also to be done through some macro code.

Plz suggest coding for this.

Regards

 

[SirJB7]

Hi, Brijesh!

Premium is given on In&Out sheet of FileA as 12000.
Now suppose I have taken Term of 1 Yr. On the Calc sheet of FileA the annuity factor for Term 1 year is 1. So, for Term 1Yr, Corpus = Premium*Annuity Factor = 12000*1 =12000
Now this Corpus (12000) is to be put on In&Out sheet of FileB in cell C6 (U have given formula "=[FileA.xlsx]Calc!$H$2"). As soon as we put this value in Cell C6 on sheet In&Out of FileB, Income of
0.129505 will appear automatically in cell C12 on sheet In&Out of FileB. Now this Income (0.129505) is to be put against Term 1Yr on sheet In&Out of FileA (i.e. cell C9 on sheet In&Out of FileA is to be allotted value 0.129505).

This whole process is to be repeated for Term 1 to 10.

Maybe I'm dumber as usual, but I don't understand what do you want to do regarding the Term 1 to 10, would you post the manual examples for 1st 3 terms? Thank you.

Further I cannot assign formula "=[FileA.xlsx]Calc!$H$2" to cell C6 on sheet In&Out of FileB due to some reason. So this work is also to be done through some macro code.

I've done that with your uploaded files. Try downloading them to any local folder other than Temp, opening both in Excel and then trying the formula.
Regards!