I have set up drop-down lists on the "Hardware" sheet which look at my stock tables on the "Stock Sorting" sheet and makes the table corresponding to the table the drop down menu is housed in their range to give the user an option of which product they want to select.
I currently have two stock systems: the first stock system I have is on the "Stock Table" sheet which is one table that holds all of the stock I have. My second stock system is on the "Stock Sorting" sheet which looks at the first stock table on the "Stock Table" sheet and separates the stock into tables for each product type. For example the stock table on the "Stock Table" sheet will hold all of the motherboards, CPUs, GPUs ETC.... the second stock system on the "Stock Sorting" sheet looks at first table and pulls out all of the motherboards and puts them in their own table, all of the CPUs and puts them in their own table ETC...
The VBA code I am using to update the second stock system every time stock is added to the first seems to ruin the drop-down lists I am using. The code looks at the first table on the "Stock Table" sheet and for the motherboard table on the "Stock Sorting" sheet it copies all of the motherboards and pastes them over the contents of the the table so if motherboards have been taken away from the main table the motherboard table will loose those motherboards and if motherboards have been added to the first table the motherboard table will gain those motherboards. When this is done the drop-down lists which are using the tables on the "Stock Sorting" sheet as their range are broken and when looking at what their range is set at this error is displayed "='Stock Sorting'!#REF!". The drop-down lists int the hardware section of the "hardware" sheet are meant to have this range when not broken "='Stock Sorting'!$C$5:$C$1000". The code below is triggered every time the user form to add stock is used. Is there anyway from stopping the drop-down menus range from being broken or code that updates the range when the code that breaks them is used?
Here is the workbook and code to allow you to help me and understand the problem:
Drop-Down Lists.xlsm
Bookmarks