Loop through list on one sheet and return corresponding values from another sheet

I need to write a script that can loop through a list of materials on a sheet called "InventoryHealth", beginning at cell A3 and continuing down through cell A200 (or until a blank cell is reached) and for each material listed, perform a lookup to another sheet called "PastedPivotData" to a material list beginning in columnC, and return the inventory qty values from column E (same table) into the next available blank cell (to the right of the part number) in the first sheet. Each week, another column in sheet "InventoryHealth" is to be populated using this script. Manually pasting the data may cause problems if the two lists don't line up exactly from week to week, and built-in VLookups won't necessarily work because the data from prior weeks must remain static. Only the latest weekly data needs to be populated (see example image of the cells to be populated).

Some background: The weekly table is meant to illustrate the current and past health of inventory for selected items through the use of conditional formatting based on target and minimum safety stock levels for each part number. I just need a way to automatically populate the weekly values based on the most recent downloaded data in the "PastedPivotData" sheet. Would anyone take a stab at how this code would work? Once I have an idea as to how it will work, I could modify the code to work with some other sheets for different mfg plants. Thanks for any suggestions!

InventoryHealth.gif

Nice picture !