If I compile my code from the VB IDE, it doesn't appear to do much
at all. At least, there are no error  messages but also no "build
complete" message. The only thing that happens is that the 'compile
<project>' option  is greyed out.

You mentioned 'Option Explicit' at the top of the module. I'm not
familiar with this, what does it do ?

If I debug my error, my rangeText variable is as I expect, but if I
then click to my workbook  - which has already been renamed to
the 'Save As' filename - the named cell range that rangeText refers
to does not exist if I inspect the range names using 'Insert - Name -
Define', in fact none of the ranges from my original workbook
exist. It is as if Excel has not yet copied them over yet.

I'm pretty new to this VBA stuff, so please forgive some of my
queries.