Link to home
Start Free TrialLog in
Avatar of Balogh Marcell
Balogh Marcell

asked on

Copy values from another workbook based on div. criteria

Hi,
I am looking for a VBA solution for the following problematic:
I have one Master file and want to collect data from about 20 different excel workbooks (slave workbooks). The Master file consists of the following 13 sheets: "Home", "Jan 2018 Overview", "Feb 2018 Overview" , ... , "Dec 2018 Overview". The slave workbooks include always the following sheets: "Jan 18", "Feb 18", ... , "Dec 18" and further 3 sheets which are not relevant now.
Each of the relevant 12 sheets of the Slave workbooks includes in their cells "A38" to max. "A1200" different projects ("Project A", "Project B", etc.).
In the Master file's "Home" sheet I would like to list up (define) max. 10 different projects: "Project X", "Project Y", etc. Furthermore, I have a dropdown on the "Home" sheet with the following possibilities: "Jan 2018", "Feb 2018", ..., "Dec 2018". If a month (e.g. "Jan 2018") is selected from the Drop-down and I push a button on the "Home" sheet I would like to list automatically all the rows from all the 20 slaves' "Jan 2018" sheets where one of the 10 pre-defined project is standing in the column A (A38:A1200) into the Master file "Jan 2018 Overview" sheet starting from the 2nd row. Based on the drop down selection I would like to have the possibility for this automatical summary for the other months as well .
Target would be not to open all the slaves (is it possible wothout opening them copying values?) - I think it will be faster and even what I think is that the solution is easyer with Autofilter function instead of loops.
Unfortunately I can't find a working solution I hope you could support me.
I appreciate your help.
Marcell
SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Balogh Marcell
Balogh Marcell

ASKER

Hello byundt,
Thanks for your quick response. Unfortunately I am a beginner with VBA and could realize only a small part of the functions.
I attach the two simplified documents, at the moment without macro.
I appreiate your support and advise.
MarcellSlave-.xlsxMaster-.xlsx
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
WOW thanks a lot it looks good!
I tried to run it and got some feedback.

The slaves have actually .xlsm extension, I have changed this in your code to .xlsm (row 18) but with this it does not work - I got an error message that there is not enough memory available (I tried only with 1 Slave, so I guess with 20 it should be even more difficult). Each slave has got about 1 Mb. In the slaves I have the macro to send automatic email-summary on the slave's sheets' content to the proper people.

Sometimes I have too much comments and longer project names, is it possible to increase the width of the cell (column A andF in the master) automatically (to adapt it to the longest text)?

The projects in the slaves are choosen from a dropdown and I would like to copy only the value in to the Master, should I do with Paste special?

I tried to run the macro with 3 slaves and I only got the data into the Master Overview sheets from one slave - always from the same. I do not know what can be in the background of this effect - somehow the macro does not consider the other slaves.

I have hyperlinks in the slave documents and as I run the macro he requests me always to let update the links although I have set automatical update of hyperlinks in excel. Interesting, that as I tried the macro with 3 slaves I had to click 3 times to update the hyperlinks - so I guess the macro realized that I have 3 slaves but somehow the data was only transferred from one slave.

How is it possible to extend the document for the next years? Does it work if I save the documents as "save as" and rename the sheets to "Jan 2019", etc.? Will be the macro's way of working influenced?

Thanks again byundt for your great support!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
It works quite good, I am surprised how fast he is!
However, I got always a failure message at the following row:

        Set rgTarg = .Range("A37").CurrentRegion

I think because of the protected worksheets: in the slaves all of the worksheets are protected (password: "PMO"). This is built in the slaves, when the document will be opened, the sheets will be protected (in case somebody unprotected them during last usage). Thus, I think ideal solution would be to unprotect them with this Master-macro in order to get the overview and do not protect again when the macro finished as it will be protected anyway at the next opening. What do you think?
Another question I wanted to ask: I tried to run the macro when one slave was open  but I got the message: if I run the macro the not-saved work on that slave will be lost. I would prefer not to get this message but to run the macro and take the "latest-saved" status of the opened slave. Is it possible at all? I am not sure.
Many thanks byundt for your countinous support.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks byundt and I am sorry for the inconveniences. In my opinion sharing the original documents would have caused even more misunderstandings as they are full with external links and restrictions.
I had one additional remark. I tested the functions and realized that if I can not find any of the selected projects in one slave, the macro copies all of the projects from that slave in to the master. In that case I expect not to copy any rows into the master. What can be in the background?
Sorry again for the misunderstanding.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Great, thanks for the correction!
I did further testing and learned, that sometimes I have empty raws on the different Overview sheets in Master. Usually only one empty raw but sometimes even 2 or 3 rows. It happens that the empty raw comes from the same slave and both below and above I have the same project listed. Sometimes I have empty raws between two lines coming from a different slave. Accordingly, I can not describe the accurate failure but do you have any idea what might be the reason? I attached the picture to show what I mean.
testing.jpg
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I am sorry, I don't get your point with COUNTA.
I made further testing and found something else. As soon as there is an empty raw in the slave, the macro do not check the below content. It means, if the first row (38) is empty, the whole slave will not be considered.
I attach the sheets, could you please check?
Master.xlsm
slave-1.xlsx
slave-2.xlsx
slave-3.xlsx
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Now it works very well, I can not find any failure anymore! Thank you very much byundt for your great support!
Now it works very well, I can not find any failure anymore! Thank you very much byundt for your great support!