Copy entire row from multiple workbooks to a master workbook if condition was met

[Malou]

Hi All,

I'm a newbie in Macros and I need help in this project.

Please see attached file to fully understand why i need.

the attached file was used by 10 users and the file was save with different filename (ex. GSTemplate_MALOU)... all of them are connected to network. The data that the user inputs was their deal/trade everyday (Please inputted data in rows 6 & 7). Each users has a list of their clients in column U6:13.

What I'd like to be done:

1. Create a button or change the code in "Copy Data" button, to copy the data in each row if the user is the "buyer (column D)" of the trade. We can determine the buyer base on the list of their clients in column U6:13

2. The rows will be copied to Master file (same format from column a to h). the master file was saved in the share folder.

3. If the other user clicks the copy data button, it will locate the next available row.

hope you can help me with this.

thanks in advance.

[JBeaucaire]

First, you can start to clean up the recorded macros to improve efficiency by editing out the SELECTing and some ACTIVATing since the recorder records the human actions that do not need to be there. VBA can send commands directly to a known range, sheet or workbook without selecting.

Here's your original macros tweaked, compare them and you'll see:

Please Login or Register  to view this content.

Then here's a new macro to match the codes to column D, grab all those rows at once and append to the workbook you've noted in the code:

Please Login or Register  to view this content.

[Malou]

Hi Jerry,

Thanks for your help. I've been waiting for someone to help me with this and now got excited to try the codes. I'll do it first thing tomorrow morning...I will let u know if this suits my needs...

Thanks again.

[Malou]

Hi Jerry,

I tried and tested the code in my template and works ok..however, I got circular reference message in my MasterBlotter file everytime I open it. I notice that the Column D turns to "0" instead of copying the value from the range U6:U13.

and then, favor please, if you can add a code to copy to the next sheet of the "MasterBlotter". This is to keep records of the daily trades.

hoping for response.

thanks and regards,

[Malou]

Hi,

I tested the code again and it doesn't do the looping. . I created a button that the user will click and the data will automatically copied to the MasterBlotter. It only copies the first row that contains the value specified in range list. It should be able to copy all rows if it contains the values in range list.

and then, if another user...click the button, it should copy the entire row if the it contains the values in his range list.

[Malou]

Help please...anyone!

[JBeaucaire]

The macro goes in a standard code module, not the Blotter sheet. I imagine you would add another button to that sheet and connect it to that macro.

No, the macro does not loop, as that would inefficient. Read through each line and analyze what it does. The macro inserts a standard worksheet formula in column Z since that column appeared unused...

The first formula that goes in Z6 is: =AND($D6<>"", ISNUMBER(MATCH($D6, $U$6:$U$13, 0)))
...and that is copied down. The result is TRUE/FALSE for the rows that match codes from column D to U6:U13.
Then the macro turns on an Autofilter to leave only the TRUE rows showing, then copies them all at once to the external workbook.

No looping.

As long as you edited the code in red to the correct external workbook, there's no reason it wouldn't export data for you the way it does for me.

[Malou]

hi!

I just copied and paste the codes and edit the the path. It works ok now... however, if another user who has the same template with different filename, copy his data to master, the previous data was overwritten. It should add the data to the next row.

As I mentioned earlier, I have 10 users who uses the same template with different filenames and list of clients (u6:u13). They should be able to add their data in master once they run the macro...would that be possible?

[cyrilbrd]

Hi Malou,

I am no specialist but to be able to "add" and not to overwrite should it be considered to add such as "Offset(Lrow,col)"? Also you mean to say that several users may be using the same macro/file (although with a different user name) at the same time and therefore might be sending each a range of data to be copied to the master?
So if yes you are looking to " copy multiple workbook into one master workbook without overwriting past information"... is thsi correct?

[Malou]

hi Cyril,

Sorry but I'm not good in codes as well.. would appreciate if you can add it on the code that Jerry created for me.

YES, You are correct that I'm copying the data of multiple workbooks to a single master file.

[JBeaucaire]

So, let's look at the code.... it does exactly what you've asked:

Please Login or Register  to view this content.

OK? These continued questions make me fearful that you're not testing it. There is no code in the macro the deletes prior data, so...

[JBeaucaire]

Hi Malou,

I am no specialist but to be able to "add" and not to overwrite should it be considered to add such as "Offset(Lrow,col)"?

And in the code given already, we use this line to find the last row with data in it looking up column B, then adding 1...

Please Login or Register  to view this content.

[Malou]

hi!

Apologies... I only have little knowledge in vba and learning... Im learning from googling and reading forums like this and appreciate people who helps people like me...thanks to you!

I tested the codes and it works well. The problem happens like for example... I created another copy of the template with the codes u created for me and save it in a different filename. 2 users will open the files and then copy the data to master. what' ive notice is if user1 sends the data first to master, it goes to row 6 and so on... If user2 sends his data to master, it overwrites the data of the second one...

I maybe doing something wrong with it... i understand your codes...maybe i'm a little confused... just don't know whats happening? I will try again tonight.. And let u know what happens... again, my apologies...

[JBeaucaire]

The only reason user #2 would overwrite user 1 is if column B is not being written into. You can edit that LR= line of code to use a different column to spot the "last row" is your data is typically empty in column B. In your example file column B was not empty.


Don't apologize for not knowing, just keep at it, you'll find what is causing this, or you can post up these "new" files and let me look at the code and files, see if something not obvious that is different is present.

[Malou]

hi Jerry,

By changing LR = line of code to Column "C" instead of "B", the code works perferctly...thank you very much for your help.

Cheers!
Malou

[Malou]

If it's not too much ask, I also need help in copying the data in every sheet of the Master Blotter? We want to maintain a daily record of the trades.

What I want is if "Sheet1" has data from Yesterday, Sheet2 will be the destination of the data the next day and so on...

[Malou]

thank you!

[JBeaucaire]

Glad you figured it out.


You need to do a better job in deciding/designing what sheet incoming data is being pasted into. Your description above is too chaotic.
Perhaps creating sheets in the master book that are dated? If the daily sheet is missing it is added... if not missing it is appended to?

[Malou]

I attached the sample "MasterBlotter" file. This is where the data being copied by all users of the template with the macro you created for me. The format was preformatted.

In Sheet1,the data was copied already by running the macro. What I need is a macro that will copy the data to the next sheet available. If possible to include the format and headings.