change sheet names...with a twist?
 

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?

change sheet names...with a twist?
 

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

--
Regards,
Tom Ogilvy

"chdirect" wrote:

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?

change sheet names...with a twist?
 

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.

"chdirect" wrote:

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?

change sheet names...with a twist?
 

Thanks. I'll give this a try.

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"chdirect" wrote:

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?

change sheet names...with a twist?
 

Thanks for your help. I'm going to try this now.

"JLGWhiz" wrote:

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.

"chdirect" wrote:

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?

change sheet names...with a twist?
 

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.


"chdirect" wrote:

Thanks for your help. I'm going to try this now.

"JLGWhiz" wrote:

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.

"chdirect" wrote:

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?