all the department workbooks are in one folder on the same drive as the employee list. Department workbooks names do not necessarily match departments in the department column. However, each department workbook, contains the names of the departments that I want to match and pull in the employee list. My plan was to use it as a look up table.

For an example, my inventory workbook will contain a range or a table that lists inventory that should be in this workbook. (e.g. batteries, tires). I need VBA code in this workbook to look at the lookup table (it will be the same range in each department workbook) and pull rows from the employee list that contain batteries and/or tires in the Department column. Department column is $G. I do not want new data to be added to the bottom. Instead, I would like old data to be deleted and new data to be added over it.

Great questions. I didn't think about providing that info...oopsies.

Does that make sense?