Forum Discussion
VBA code to find matching data in other sheets
May ask, now that I've seen the sample, why there are more than 200 sheets in each of which cell L8 needs to be filled with that address data from the database sheet?
I don't mean to be impertinent in asking. There may be entirely legitimate reasons, and if so perhaps a macro is indeed the solution. I'm not a macro person; I am more of a design person who strives to use Excel with its built-in functions, often finding that there are far more elegant solutions than (what I will refer to as) the "brute force" of a macro.
So in this case, where all of the subsequent sheets are exactly the same format, it makes me wonder whether they could all be consolidated into:
- another single database, where it would be easy (as you note) to write a simple LOOKUP function for the data to be retrieved from the address database, PLUS
- a nicely formatted "dashboard" that mimics the layout you have on each of these individual sheets now
As I said, there may be a reason why that can't be done, but I view part of my (volunteer) job here in the techcommunity to raise such questions about design rather than simply give answers, where I think such questions are warranted.
Thank you for your reply, please find attached the mock-up file.
I would probably need a macro because I have more than 200 sheets to run the same action.
For example I need to fill the cell L8 of every sheet with the address data in range D2:D6 of addressDATA sheet according to the name matching (partially) in the range B2:B6.
I would just use a VLOOKUP function if i had only few sheets, but it's not the case.
Thank you in advance for your help 😄
May ask, now that I've seen the sample, why there are more than 200 sheets in each of which cell L8 needs to be filled with that address data from the database sheet?
I don't mean to be impertinent in asking. There may be entirely legitimate reasons, and if so perhaps a macro is indeed the solution. I'm not a macro person; I am more of a design person who strives to use Excel with its built-in functions, often finding that there are far more elegant solutions than (what I will refer to as) the "brute force" of a macro.
So in this case, where all of the subsequent sheets are exactly the same format, it makes me wonder whether they could all be consolidated into:
- another single database, where it would be easy (as you note) to write a simple LOOKUP function for the data to be retrieved from the address database, PLUS
- a nicely formatted "dashboard" that mimics the layout you have on each of these individual sheets now
As I said, there may be a reason why that can't be done, but I view part of my (volunteer) job here in the techcommunity to raise such questions about design rather than simply give answers, where I think such questions are warranted.