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
43 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Outlook Free & Paid Tools
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

747 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

13 Experts available now in Live!

Get 1:1 Help Now