Pivot Table cell/range to change based on drop down menu

I have the same register that most of my questions have derived from, still giving me some grief.

I've altered my transfer page (the page that gets pushed to the word report) to be a pivot table.

The Control Sheet has a drop down field which picks up the NCR Register number. How can I get the pivot table (Registered Number (Row/Cell)) to look up the control sheet drop down field and filter (or alternatively, on selecting, how can I get the drop down field to automatically update the pivot table), so that my VBA code can push the information to the word report?


Pages are: Control Sheet, Transfer Sheet
Control Sheet = D7 (for drop down)
Transfer Sheet = A7 *Registered Number Heading* A8 Actual Registered Number

Pip