Solved

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

Posted on 2016-07-26
3
69 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

749 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