Macro - Fixed text code needs replacing with variable text

[steven.holloway]

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))"

 

[DS-NTE]

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

 

[Dave Peterson]

Dim ShtName as string
shtname = "Sheet1"

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

But there are some characters that can't be used in the name:= portion. You'll
want to be careful.

The apostrophes inside the referstor1c1:= portion will let any valid name work
there.

 

[steven.holloway]

Thanks DS & Dave, exactly what I needed.

Many thanks again
Steve