Im trying to bring across the value of a cell after it finds the set reference and matches some text

Thanks to the help of this forum we were able to create a macro to bring across my desired cells using .SpecialCells

Example: email.xlsm

Its working beautifully but now i’m trying to use the “reference numbers” eg. 01806220120914/0195

Then search/find for the occurrence where the “reference numbers” - 01806220120914/0195 equals that set reference number 01806220120914/0195 in column “C” of my “Bulked Letters” sheet and where the first 7 letters of "AC" equal "PAYDATE".

Then bring across this value into my other workbook.

The move to the next reference number...

Any ideas welcome please

end result.JPG
letters bulked.jpg