Create VLookups using VBA on Multiple Excel Sheets

I would like to use vba to loop through all the worksheets in an Excel workbook to create vlookup formulas on a master sheet. The 3 columns on the master sheet are J, K and L and formulas are below for the master sheet. There are 150 sheets with varying  number of rows. The vlookup formulas are on the master sheet and pull data from the 150 sheets. Also, some of the worksheet names have special characters. Each record has a unique ID in column A which is the lookup value.


 Can you provide code that might accomplish this. Thanks


sample vlookup for the first sheet of 150
 Column (J) Conflict is Justified? (Y/N) :    =VLOOKUP($A$2:$A$10202,Adrian_Luster!$A$2:$L$47,10,FALSE)
 Column(K) Justification   :=VLOOKUP($A$2:$A$10202,Adrian_Luster!$A$2:$L$47,11,FALSE)
     Column(L) Mitigating Controls for Conflict  :  =VLOOKUP($A$2:$A$10202,Adrian_Luster!$A$2:$L$47,112,FALSE)
shieldscoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ken ButtersCommented:
Are the vlookup examples you show above working correctly?   It is my understanding that the first parameter of the vlookup function is the value that you want to search for.   Your first parameter is a range --- $A$2:$A$10202, which is most definitely not a value.
shieldscoAuthor Commented:
Yes they work correctly

Vlookup on the Master Sheet:

 $A$2:$A$10202 is the lookup (Unique Value in column A)

Adrian_Luster!$A$2:$L$47 is the table_array (note; the table_array will be different for each worksheet depending on the number of records on that sheet)

10 is the column_index_num

False is the Range_lookup
Ken ButtersCommented:
can you post example workbook with the master and at least 2 sheets, with vlookup examples for both additional sheets?
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

shieldscoAuthor Commented:
shieldscoAuthor Commented:
Ken are you able to handle this task?
Ejgil HedegaardCommented:
You don't need VBA to do this.

These sheets have a "-" in the sheet name
Karen_Eilola-Miller
Rhonda_Belser-Davis
Soneata_Rivers-Lewis
Starlette_Gaskin-McDaniel

When replacing "-" with "_" in the sheet name these formulas in J2, K2 and L2 do the job
=VLOOKUP($A2,INDIRECT(SUBSTITUTE($C2,"-","_")&"!$A$2:$L$1048576"),10,FALSE)
=VLOOKUP($A2,INDIRECT(SUBSTITUTE($C2,"-","_")&"!$A$2:$L$1048576"),11,FALSE)
=VLOOKUP($A2,INDIRECT(SUBSTITUTE($C2,"-","_")&"!$A$2:$L$1048576"),12,FALSE)
Delete existing values in column J, K and L before inserting the formulas, then the table functions fill the column.

Then you don't need a new formula for each ERP_USER_NAME.
Names in ERP_USER_NAME in the Master table are not changed.
SOD-Conflict-Review-Mock-Up-A.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ken ButtersCommented:
I absolutely do NOT understand how this is working:
=VLOOKUP($A$25:$A$1048576,Adriana_Moore!$A$2:$L$1048576,10,FALSE)

That first parameter is supposed to be a single value.
shieldscoAuthor Commented:
Works good... however the master sheet is dynamitic.
I would not want to copy formulas each time the master changed.... the range is variable
shieldscoAuthor Commented:
Ejgil - The Master Excel sheet will have a data connection with an Access db table
Ejgil HedegaardCommented:
Formulas in a table automatically adjust to the table size when the table is updated.

Clear the column values before applying the formula in the first row.
When you enter the formula it is automatically copied down to the bottom of the table.

Only when you add more sheets, remember to replace "-" with "_" in the sheet name.
Rob HensonFinance AnalystCommented:
Ejgil:

With allowance for the "-" in the sheet name:

=VLOOKUP($A2,INDIRECT(SUBSTITUTE($C2,"-","_")&"!$A$2:$L$1048576"),10,FALSE)

Is pretty much what I suggested on the other question from shieldsco on this same subject.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28643806.html

If we now have the formula sorted, what are you trying to achieve by using VBA? As mentioned above, as the list is set as a table, the population of the formulas should be automatic.
Ejgil HedegaardCommented:
Rob:
I have not read the previous question.
No reference here, so how should I know about it.
You are right, that is more or less the same.
Is your last question to shieldsco?
I don't see any reason to use VBA

Looking at it again the formula can be made to accept sheet names with "-"
With the table column name as reference to lookup value and sheet name, and columns instead of range on the sheets, the formula for column J is more general
=VLOOKUP([ID],INDIRECT("'"&[ERP_USER_NAME]&"'!$A:$L"),10,FALSE)
similar for K and L, with reference 11 and 12.
SOD-Conflict-Review-Mock-Up-B.xlsm
Rob HensonFinance AnalystCommented:
Ejgil - yes the questions were aimed at shieldsco.

Wouldn't have expected you to know about the other question, just spotted very similar question title to the one I ahd been working so flagged it. I was tempted to report it as a duplicate but last time I did that the mods declined marking it as a duplicate because there was sufficient difference in the discussions in each for them both to be worthwhile.

I suspect the VBA requirement is for checking that all sheets and all IDs are covered in the MASTER sheet.
shieldscoAuthor Commented:
Rob - formula does not take into account dashes
shieldscoAuthor Commented:
Ejgil: #REF! on names like Karen_Eilola-Miller, Rhonda_Belser-Davis. It looks like the names with dashes...

,
Ejgil HedegaardCommented:
The formulas in the last file, revision B, accepts all legal sheet names, including dash, space, plus etc.
And the sheet names can be exactly as the names in ERP_USER_NAME.
What is your formula?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.