The layout of your workbook is not good
-the way it is currently laid out will make it very messy to change anything later
- VBA is not like Excel - if you insert a row in Excel , then Excel adjusts all the other cell references - VBA cannot do this
(if we tell VBA to put a formula in G18, and we insert a row above A3, VBA will not know that it now should put the formula in G19 and that it needs to add another row into the sum)
- there are totals at the bottom which drive average calculations at the top, and data is added into rows between the two
- if you add a "new" chemical, then you will need to rewrite some of the VBA
- that is storing up lots of potential problems for the future

I suggest your daily data is put into one sheet and that the information you want to see is generated as you want it using a lookup formula
- it will look exactly the same, but it means your VBA will be much simpler
- it also means you will be able to change things yourself if necessary

I will post my suggested approach tomorrow