Data report/collection

New Contributor



I am trying to create one centralised spreadsheet with multiple data (normally 3 spreadsheets). Is there a way of creating a macro button that, when pressed it 'pulls' all data containing certain text? 


-More info-


Within the document there are multiple sheets for about 40 teachers and their own students details. some of these students are under different companies, so the macro is to be able to pull all the students details from all the sheets into one place to be able to report on. 


I am hoping I have explained this correctly! and hoping someone can help! Thanks!

6 Replies


With your permission, I recommend that you insert a file (without sensitive data) and discuss your topic on the basis of this file.

Knowing the Excel version and the operating system is always an advantage.
In your explanation, some questions arise, such as,

from which column or area should the values be copied and in which column area should they be copied?
Are all values in all worksheets in the same column? ... with the same text in the heading?


These are just a few of the questions that come to my mind so quickly.

If you want to do it with VBA you have to specify exactly from where, how, where to.



Thank you for your understanding and patience



I know I don't know anything (Socrates)





yes I totally understand its easier to see it, I have attached it now and taken out sensitive data. This is a proposal spreadsheet I am working on for a new job I start in September. 


-so on the first sheet 'VRW Tracker' I want to add in a button that when clicked it will pull all the students who are 'AIM' which is under columns F in the teachers sheets 


-there will be eventually up to 60 teachers along the bottom and each teacher will be able to go in and update their students progress, this is why i wanted all the data split between the sheets so its easier for them, 


-my new boss would like a different spreadsheet for each Location of study (column F) but I need a way of reporting all the data from across multiple sheets. Or to have one sheet named 'AIM' which will update with any new students from there.


Hope this makes more sense! Open to other suggestions too!


Here is a small proposal for a solution without VBA.
Simple table equipped with IF function, can be filtered as required. T

hus everyone has their own picture of data as they want it to be.
If this suggested solution does not match your plans, please inform us.


Thank you for your understanding and patience


Wish you a nice day.



I know I don't know anything (Socrates)

Thank you for your suggestion. This will work in terms of just collecting names but ideally I wanted the whole row of information pulled across. This would mean we could send this information to 'AIM' for reporting purposes.

Thank you.
Give me a day ... I won't send you a suggested solution until tomorrow. A lot to do at the moment and I won't find the time. Thank you for your patience and understanding in advance NikolinoDE


I did not forget it :)).


Here is my suggested solution with VBA.

You only need to copy the columns in the "AIM" sheet, each with an assessor, and duplicate them depending on the number of sheets you have.

In the sheet "AIM" you will find a button at the top right, press it and all the data from all assessors will appear.

Please name the assessors exactly as you will name the sheets, otherwise it will not work.


I hope that you will get ahead in your project.


For further questions I am gladly at your disposal.


Thank you for your patience and understanding.