change sheet names...with a twist?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need help creating a 2 part formula, which will allow me to change all of
the sheet names in my file to the value in cell A1 on each sheet. There's a
twist though...I want Excel to ignore the first 4 characters in Cell A1 on
each sheet before changing the sheet name.

So, if Cell A1 on Sheet1 contains the value 001=Farming, I would like the
sheet name to be called 'Farming'. I can extract 'Farming' in Excel using
the following formula: =MID(A1,11,LEN(A)-4) but I don't know how to do this
in VB.

Second, I'd like to create a function that will change all of the sheet
names in the file to the value in Cell A1 on each sheet.

Can anyone help?
 
You formula didn't work for me, but a slight change:

? Range("A1").Value
001=Farming
s = evaluate("MID(A1,5,LEN(A1)-4)")
? s
Farming


if you don't want to use evaluate

s = Mid(Range("A1"),5,len(Range("A1"))-4)
? s
Farming
 
The basic code to change all sheets names

Sub chngNm()
For i = 1 to ThisWorkbook.Worksheets.Count
Worksheets(i).Name = Cells(1, 1).Value
Next
End Sub

If all of your values in A1 of each sheet begin
with 00# = $$$$$... Then you can use

Worksheets(1).Name = Mid(Range("A1").Value, 7, Len(Range("A1").Value) - 6)

That should all be one line in case it wraps on the
newsreader.
 
Thanks. I'll give this a try.

Tom Ogilvy said:
You formula didn't work for me, but a slight change:

? Range("A1").Value
001=Farming
s = evaluate("MID(A1,5,LEN(A1)-4)")
? s
Farming


if you don't want to use evaluate

s = Mid(Range("A1"),5,len(Range("A1"))-4)
? s
Farming
Click to expand...
 
If you don't have spaces before and after the = sign in A1 then
change the -6 to -4 in the line

Worksheets(i).Name = Mid(Range("A1").Value, 7, Len(Range("A1").Value) - 6)

That number represents the number of characters and spaces before the actual
text. But you already knew that.
 
Back
Top