Solved

how to copy rows from 2 spreadsheets into one based on a condition

Posted on 2016-07-26
  • MS Excel
  • VB Script
  • Spreadsheets
  • MS Office
  • Visual Basic Classic
  • +2
3
51 Views
Last Modified: 2016-08-27
Hello,

I've been working on an inventory spreadsheet for 2 pharmacies for a hospital in Africa which exchange items and drugs every now and then.  Every pharmacy is using 1 spreadsheet for every drug which is structured in the same way. One spreadsheet represents one drug. I am not very experienced in coding macros in vb this is why I need your help to realise that goal
I would like both excel files to be able to copy rows from the first sheet “stock card” into one based on a condition with the help of a button executing a macro in every file.
 Sometimes number of units of one drug gets transferred from pharmacy1 to pharmacy 2 and vice versa (via first sheet: "Stock card")
To identify in both files what transactions in the list concern these 2 actors, I introduced a helper column starting in cell P9 which turns to "MP" for every concerned row.
In the next phase I d like the concerned rows in BOTH files (which share the value "mp" in column P) to be copy pasted to a master worksheet inside of the spreadsheet of pharmacy1, let’s call it "transactions between pharmacies". I think there’s no way to do that without a macro.
As I ll keep adding more data into both spreadsheets, it is important that I can run the macro many times, adding more and more information to the spreadsheet "transactions between pharmacies". So the challenge here would be to replace all the information in that spreadsheet every time I run the macro.
To illustrate what I want to achieve I manually collected the data I want from both files into the worksheet “transactions between pharmacies”. I d need this to work automatically.
Thanks a lot in advance for your help, I hope it can be done!
paracetamol_pharmacy1.xlsm
paracetamol_pharmacy2.xlsm
0
Comment
Question by:Emmanuel S
3 Comments
 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 41733728
Try attached.
Button on sheet "Transactions between pharmacies" to run the macro (in module2).

Every run clears existing data in columns A:P on transaction sheet, and then inserts new values.

All rows on sheet STOCK CARD in workbook 1 (with the macro) searched for MP in column P.
When found, format and values copied to transaction sheet.
Then STOCK CARD sheet in second workbook searched for MP in column P, and same date in column A as found in workbook 1.
Format and values copied to transaction sheet to next row.

Workbook 2 closed after transfer, but if it was already open, it is not closed after transfer.
When workbook 2 is opened, links to other workbooks are not updated (can be changed).
paracetamol_pharmacy1.xlsm
1

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now