Solved

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

Posted on 2016-07-26
3
84 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 22

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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Part One of the two-part Q&A series with MalwareTech.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Starting up a Project

623 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