Naming a sheet from formula result

[Rob]

I would like to name a sheet from the result of a formula that gets is
result from a cell in a worksheet, the following is my routine but first
writes the formula to cell A1 in the worksheet, is there a more appropriate
method?

Dim shname As String
Range("A1").Formula = _
"=""DD""&Left(Right(A2, 10), 2) & Right(B2, 6)"
shname = Range("A1").Value
Range("A1").ClearContents
Sheets(1).Name = shname

Thanks, Rob

[Bob Phillips]

I don't think you need a formula as there are VBA Left and Right methods. So
how about


Sheets(1).Name = "DD" & Left(Right(A2, 10), 2) & Right(B2, 6)



--
HTH

Bob Phillips

"Rob" <anonymous@discussions.microsoft.com> wrote in message
news:ukp$E$t%23EHA.2876@TK2MSFTNGP12.phx.gbl...
> I would like to name a sheet from the result of a formula that gets is
> result from a cell in a worksheet, the following is my routine but first
> writes the formula to cell A1 in the worksheet, is there a more
appropriate
> method?
>
> Dim shname As String
> Range("A1").Formula = _
> "=""DD""&Left(Right(A2, 10), 2) & Right(B2, 6)"
> shname = Range("A1").Value
> Range("A1").ClearContents
> Sheets(1).Name = shname
>
> Thanks, Rob
>
>

[Rob]

Bob,

Thanks for this, I amended to include Range so as I had set Options Explicit

Sheets(1).Name = "DD" & Left(Right(Range("A2"), 10), 2) &
Right(Range("B2"), 6)

"Bob Phillips" <phillips@tiscali.co.uk> wrote in message
news:%23CxzhEv%23EHA.208@TK2MSFTNGP12.phx.gbl...
>I don't think you need a formula as there are VBA Left and Right methods.
>So
> how about
>
>
> Sheets(1).Name = "DD" & Left(Right(A2, 10), 2) & Right(B2, 6)
>
>
>
> --
> HTH
>
> Bob Phillips
>
> "Rob" <anonymous@discussions.microsoft.com> wrote in message
> news:ukp$E$t%23EHA.2876@TK2MSFTNGP12.phx.gbl...
>> I would like to name a sheet from the result of a formula that gets is
>> result from a cell in a worksheet, the following is my routine but first
>> writes the formula to cell A1 in the worksheet, is there a more
> appropriate
>> method?
>>
>> Dim shname As String
>> Range("A1").Formula = _
>> "=""DD""&Left(Right(A2, 10), 2) & Right(B2, 6)"
>> shname = Range("A1").Value
>> Range("A1").ClearContents
>> Sheets(1).Name = shname
>>
>> Thanks, Rob
>>
>>
>
>

[Bob Phillips]

of course ... should have spotted that :-)

Bob

"Rob" <anonymous@discussions.microsoft.com> wrote in message
news:OGwRXHx%23EHA.1400@TK2MSFTNGP11.phx.gbl...
> Bob,
>
> Thanks for this, I amended to include Range so as I had set Options
Explicit
>
> Sheets(1).Name = "DD" & Left(Right(Range("A2"), 10), 2) &
> Right(Range("B2"), 6)
>
> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> news:%23CxzhEv%23EHA.208@TK2MSFTNGP12.phx.gbl...
> >I don't think you need a formula as there are VBA Left and Right methods.
> >So
> > how about
> >
> >
> > Sheets(1).Name = "DD" & Left(Right(A2, 10), 2) & Right(B2, 6)
> >
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Rob" <anonymous@discussions.microsoft.com> wrote in message
> > news:ukp$E$t%23EHA.2876@TK2MSFTNGP12.phx.gbl...
> >> I would like to name a sheet from the result of a formula that gets is
> >> result from a cell in a worksheet, the following is my routine but
first
> >> writes the formula to cell A1 in the worksheet, is there a more
> > appropriate
> >> method?
> >>
> >> Dim shname As String
> >> Range("A1").Formula = _
> >> "=""DD""&Left(Right(A2, 10), 2) & Right(B2, 6)"
> >> shname = Range("A1").Value
> >> Range("A1").ClearContents
> >> Sheets(1).Name = shname
> >>
> >> Thanks, Rob
> >>
> >>
> >
> >
>
>