Try this (untested):

myName = ActiveSheet.Name
ActiveWorkbook.Names.Add Name:="Oracle_Travel_" & myName, RefersToR1C1:= _
"=OFFSET(myName & !R12C2,0,0,COUNTA(myName & !C2),COUNTA(myName &
!R12))"


HTH
knut


"steven.holloway" skrev i melding
...
I would like to change the below code so that all of the current fixed text
typed "Sheet1" are replaced with a variable text and the variable text is
equal to the ActiveSheet.Name

I can see there are two parts too resolving this 1) the variable text will
be only part of the define name and 2) the variable text in the formula
entry
within the define name

Any help with this would be fantastic
Many thanks
Steve

ActiveWorkbook.Names.Add Name:="Oracle_Travel_Sheet1", RefersToR1C1:= _
"=OFFSET(Sheet1!R12C2,0,0,COUNTA(Sheet1!C2),COUNTA (Sheet1!R12))"