# Excel Macro - Copy data from cells in WB1 to WB2, then return calculated value from WB2 to WB1

I am in need of help.  I have WB1 which contains a listing of data
A              B
2  15.00       30.23
3  14.37           32.00
4  38.00           67.00

I need to open WB2 and plug the data from WB1 into WB2 - the data needs to plug into the same cells on WB2 every time (so WB1 will need to loop, but go to the same place on WB2 every line).

WB1 A goes to WB2 F11
WB1 B goes to WB2 F6
Once A and B are plugged in, another cell on WB2 creates a percentage value (I have those formulas figured out).  The result is on WB2 F26 .  In the case of row 2 the value is 6%.

My final step is to get the percentage value from WB2 cell F26 back to WB1 column C as a value.
A              B              C
2  15.00       30.23     6%
3  14.37           32.00     7%
4  38.00           67.00    10%

I was able to accomplish what I wanted by recording a macro as I did the operation - however that is incredibly lengthy as each row is about 20 lines of copy paste code and since I might easily have 300 rows, that is not feasible.  I need the macro to loop until WB1 A is blank.

Any help would be appreciated and if clarification is needed, let me know.
Thanks!
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
If i read this correctly you are using Wb2 to calculate a result using columns A/B from WB1 and then posting it back to column C of WB1?

There are many possible ways to achieve this which are dependent on how you receive the workbooks, who manages the data and most fundamentally of all WHAT are you trying to achieve.

For example:
we could add a function in WB1 that replicates the calculations in WB2, this would be stored in WB1 and referenced as '=myFnWB2calc(a2:b2) (or example). WB1 would therefor be independent of WB2.

If WB2 holds data that is needed for the calculation but that data is relatively static would it be useful to create WB2 as an add-in that is loaded every time WB1 is opened and then the add-in would create the necessary values for column C?

We could, as a first step, copy WB2 into Wb1 as a sheet and then create links between the two to achieve the same result as above.

Its not clear what you mean by 'WB1 A is blank' are you saying that by the end of the run you will have columns B and C populated but not A?
Or do you mean it should loop through and create the value in C but leave the rest of the data intact?

So can you clarify:
1. Do you have control over WB1 or does it arrive from somewhere else for update?
2. what happens once C is populated?
3. Is the information in WB2 confidential so it can't be copied into WB1?
4. how should the data look in WB1 once column c is filled (ie: is A still present?)
5. Is it possible to post samples explaining what you would like to see
Author Commented:
Thank you - Terrific Questions.
First paragraph, yes  that is correct.

This is actually a macro to calculate sales commissions and WB 2 contains a look-up table to calculates the % based on spread and annual salary - so I can't really replicate the functions in a cell.

But WB2 is extremely static and the only things that will change are the bill and pay that are getting input to determine commission percent.  I would think either an add-in or copy to the sheet might work.  Currently my macro is already opening the WB1, so opening another may not be that big of a deal.  I have done a lot with macros on one sheet - but not very much where I am referencing another sheet/WB.

As to not clear -I do mean it should loop thru and create the value in C and leave A and B intact.  I would like like it to loop until the end of the data set is reached.

So
1 - I actually have created WB1 from a previous macro that took a rough data dump and cleaned it up - so I have total control over all data.
2 - after C is populated I have more steps to do, that will be more formulas in WB1  to create a D and E column and a saving function.
3 - WB2 has restricted access so it can't be a sheet on the final WB1 file once it is saved, but it could be pulled in, used and deleted again before the final save if needed to be.
4 - yes still a percent
5 - As for a sample, I can't really post too much of my information.  But what I did put at then end of my question pretty much sums it up.  A and B will be number format w/2 decimals (dollars w/out the dollar signs) and Column C will be a percent - that percent is anywhere from 3% to 10%

I hope this answers everything...Thanks for looking at this!
Commented:
Ok it sounds like you've done some VBA so here's some pointers  and I'll work on a more complete solution when i have a little more time later today:

To open/reference another sheet:
``````Dim firstBook As Workbook               'WB1 for example
Dim firstSheet As Worksheet            ' worksheet in WB1
Dim Otherbook As Workbook                      'WB2
Dim OtherSheet As Worksheet                   'Worksheet in WB2

'I'm assuming here that you are running the macro from within WB1

Set firstBook = ActiveWorkbook               ' we can now refer to WB1 as firstBook in the code like this
Set firstSheet = firstbook.Sheets("Current")     ' and now firstsheet means the tab called 'current' in WB1

'open WB2
'when you use open like this Wb2 will appear in foreground, which doesn't matter but looks untidy
' so I'll show you how to hide it later

Set otherBook = Workbooks.Open("WB2", False, False)   'NB: the filename should be fully qualified
Set otherSheet = otherBook.Sheets("Sheet1")                 ' now other sheet refers to Sheet1 or WB2

'then we can manipulate between the two easily for example:

' - we would be setting up some kind of loop here

Salary = firstsheet.range("a2")
Commission = firstsheet.range("b2")

othersheet.range("f11").value = salary
othersheet.range("f6").value = commission

othersheet.calculate

percentage = othersheet.range("f26").value

firstsheet.range("C2") = percentage

'and closing the loop here

otherSheet.close (false)                   'close wb2 without saving
``````

Another option is to create a user defined function but from your answer I assume the data in Wb2 is quite sizeable and cannot be stored in WB1 for confidentiality reasons so I haven't pursued that
Author Commented:
I do have some experience, yes.  I'll take a look at what you have posted and will see what I can do.   And yes your assumptions on the bottom about the both the size and confidentiality is correct.

Thanks, I'll update with any success I might have :-)
Commented:
typo because I was rushing to go to a meeting:

this part
``````Salary = firstsheet.range("a2")
Commission = firstsheet.range("b2")
``````

should be:

``````Salary = firstsheet.range("a2").value
Commission = firstsheet.range("b2").value
``````
EngineerCommented:
Here is a non macro solution:

supposing your data is laid out as shown

Then
For Book 1
in D2 enter the formula   =TEXT(B2,"00000.00")&TEXT(C2,"00000.00")
copy this formula down.
in E1 enter the formula =G2
in G2 enter the formula   =[Book3]Sheet1!\$F\$26         (This basically is to pick the result of F26 on the other workbook)

For Book 2
Enter this formula in A1     =[Book2]Sheet1!\$G\$1
Enter this formula in F6     =VALUE(RIGHT(A1,7))
Enter this formula in F12   =VALUE(LEFT(A1,7))

Back to Book1
Select D1:E4
Data > What-if-analysis > Data Table
In Column input cell enter        \$G\$1
Click OK
Author Commented:
thanks, I'm on it.   Give me a few to try to figure it out.  We all learn by trying :-)
Author Commented:
Saquib, thanks anyway, but it has to be a macro.  Once created, I will have a clerk running this for multiple people weekly.

regmigrant, on what you have so far
otherSheet.close (false)                   'close wb2 without saving
needs to be otherBook.close

I have it working, I have been trying to get the looping going and no luck yet.  I'm going to ask for further help if you would be willing.
Commented:
sorry about the further typo - but you've clearly got the skills to debug which will be useful going forward:)

here's a suggestion for a simple loop but it assumes that there are no empty rows in column a. if you need something more robust we can switch to a different method. You didn't post your changes so I've reused what's above

``````Dim firstBook As Workbook               'WB1 for example
Dim firstSheet As Worksheet            ' worksheet in WB1
Dim Otherbook As Workbook                      'WB2
Dim OtherSheet As Worksheet                   'Worksheet in WB2

'I'm assuming here that you are running the macro from within WB1

Set firstBook = ActiveWorkbook               ' we can now refer to WB1 as firstBook in the code like this
Set firstSheet = firstbook.Sheets("Current")     ' and now firstsheet means the tab called 'current' in WB1

'open WB2
'when you use open like this Wb2 will appear in foreground, which doesn't matter but looks untidy
' so I'll show you how to hide it later

Set otherBook = Workbooks.Open("WB2", False, False)   'NB: the filename should be fully qualified
Set otherSheet = otherBook.Sheets("Sheet1")                 ' now other sheet refers to Sheet1 of WB2

'then we can manipulate between the two easily for example:

Dim i as long
i = 2                                                          'start from the first row of data
Salary = firstsheet.range("a" & i).value

While Salary <> 0                                       'obviously this won't work if 0 is valid in column a or column a
'can have an empty field in which case we will use a range

Commission = firstsheet.range("b" & i).value      'i is pointing to the current row

othersheet.range("f11").value = salary
othersheet.range("f6").value = commission

othersheet.calculate

percentage = othersheet.range("f26").value

firstsheet.range("C" & i) = percentage

'+++++++++++and closing the loop here
i = i +1                                                         'next row
Salary = firstsheet.range("a" &  i).value     'get the next value from column a
Wend                                                                'stop when the next row is blank

otherbook.close (false)                   'close wb2 without saving
``````

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
here's a worked example using a mocked up version of wb2. You'll need to modify file locations.

I've also added code in to hide the opening of WB2 so you can't see the confidential information - but this isn't bullet proof, if someone managed to stop the code whilst wb2 is still open they could switch to it.

I've used a different type of loop that doesn't depend on a value in column a, it just checks for the lowest row and loops until it reaches that, you'll need to think about validating the values before passing them and/or checking if column b has more rows if a is allowed to have blanks.

Hopefully this will be enough for you to develop but come back if there's anything further

Reg
wb1.xlsm
wb2.xlsx
Author Commented: