Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sheet tab names change automatically | Excel Discussion (Misc queries) | |||
Change sheet names for charts on the fly | Excel Discussion (Misc queries) | |||
How to change sheet names via formula | Excel Discussion (Misc queries) | |||
change range names on a particular sheet | Excel Programming | |||
Code to bold names within a cell- With a twist | Excel Programming |