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

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

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!

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

Select allOpen in new window

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

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 :-)

typo because I was rushing to go to a meeting:

this part

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

Select allOpen in new window

should be:

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

Select allOpen in new window

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

thanks, I'm on it.   Give me a few to try to figure it out.  We all learn by trying :-)

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.

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

Incredibly HELPFUL!   THANK YOU!!!

Thank you, this loop is so much simpler than what I have tried in the past.  I will be incorporating it in future macro's.   What you have posted it doing everything correctly.  Now I have to figure out how to deal with the ifs and buts in the data that showed up after this macro ran correctly - surprise no one told me about them :-).  I think I will be able to handle them, but if not I'm coming back to this community.  Thank you Regmigrant for all you help!!!!  

People like you help us all work better.

Thanks for the compliments and glad I could help - good luck with the rest of the project!