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!
jenitAsked:
Who is Participating?
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.

regmigrantCommented:
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
0
jenitAuthor 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!
0
regmigrantCommented:
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

Open 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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jenitAuthor 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 :-)
0
regmigrantCommented:
typo because I was rushing to go to a meeting:

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

Open in new window


should be:

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

Open in new window

0
Saqib Husain, SyedEngineerCommented:
Here is a non macro solution:

supposing your data is laid out as shown

Data layout
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
0
jenitAuthor Commented:
thanks, I'm on it.   Give me a few to try to figure it out.  We all learn by trying :-)
0
jenitAuthor 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.
0
regmigrantCommented:
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:

'+++++++++ loop additions
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 
                                                                      'instead

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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
regmigrantCommented:
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
0
jenitAuthor Commented:
Incredibly HELPFUL!   THANK YOU!!!
0
jenitAuthor Commented:
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.
0
regmigrantCommented:
Thanks for the compliments and glad I could help - good luck with the rest of the project!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.