Bob,

I tried your solution, I think it's really close with a few tweaks
(sorry)....

Firstly, ThisWorkbook.Names.Add Name:=sName, _
how can I get it to include the sheet name for example suppose A2 has
Date Machined as the column header and the sheet name is Sheet1
then the range name would be Date_Machined_Sheet1
as a suggestion, do you think it would be best to set the worksheet
name to a variable, then call this out with sName.

Secondly, when I run the macro as written it includes the quotation
marks around the formula
="OFFSET(Sheet1!$A$2,1,0,COUNTA(Sheet1!$A:$A)-1,1)"
only after removing the quoation marks does the dynmanic range work.