Finding data

[p45cal]

tried the code but unfortunately throws up an error: PasteSpecial method of range class failed highlighting

Destn.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False

any ideas? thx

OK here.
Attached with a few tweaks.
I've left it with no data in the 'form' and the value 1065 added to cell Y1. Just click the button (Button4) to see what happens.
I've moved your Business Unit Survey to one side because the macro has to clear a non-specific number of rows below row 18 in those same columns.

I see there's a reference to Master Data File v2.5.xlsm; is this where the data on the Data sheet comes from? If so there may be no need to copy/paste that data into this workbook.
Also, I would recommend the use of Tables and perhaps also (Power) Queries to get the data in. One advantage is that tables can update themselves and shift data/tables below them up and down according to their sizes; this is the main problem behind this thread. There may even be no need for macros at all.