VBA (which applies VLookup formula to an entire "sheet 1", pulling data from another "sheet 2") when a code is entered in "Sheet 1"

[NateZona]

Dear helpful people of Mr. Excel,

I have been searching for an answer to this problem for quite some time now, to no avail.

I have an Excel 2010 workbook, which keeps track of all the supplies needed for production at my store.
Using a VLookup formula, which is applied to every cell in Column C-Column Q in a sheet called "USED", I am able to populate an entire row, with data from a table in a second sheet called "Sheet 4". This runs when I enter a code word in any cell of Column B.

This works fine, but since "USED" has a formula on so many cells, excel runs slowly whenever opening the workbook, entering data, or saving.

What I am looking for is a way to apply the formula to all the appropriate cells in Columns C-Q in sheet "USED" without putting the formula in all the actual cells themselves.

I believe I can use a VBA to automatically detect when I enter a code into Column B of "USED", and then automatically fill the appropriate data from "Sheet 4" into the appropriate row of "USED". I just can't seem to figure out how!

I am not sure how to attach my Workbook as the "HTML-Maker" section of the FAQ is confusing,
so here is some important data:

Formulas applied respectively to Columns C-Q in sheet "USED"*:
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,2,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,3,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,4,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,5,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,6,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,7,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,8,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,9,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,10,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,11,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,12,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,13,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,14,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,15,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,16,FALSE),"")

*Since each row will change the number of Column B,
"B2" will change to "B3", B4", B5", etc as the row changes.

I hope this was clear enough, and look forward to the solution!

Warm Regards,
Nate Zona

 

[NateZona]

Thanks again! Runs so smoothly now

 

[JoeMo]

Thanks again! Runs so smoothly now

You are welcome - thanks for the reply.