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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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.

785 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