how to change sheet name in cell

Microsoft Excel 2010 - complete product...
November 6, 2011 at 08:14:10
Specs: Windows 7, 6400@2.13GHz 2.13GHz/4.00GB
...Begin quote from old post...

DerbyDad03 May 1, 2010 at 08:14:04 Pacific

If the sheets are in the same order as the names on the list, then renaming them is fairly simple.

Assuming the list of names is in Sheet1!A1:An, and assuming you have n + 1 sheets in your workbook (one for each name, starting with Sheet 2, plus Sheet 1 with the list), this code will rename each sheet, in order, based on the list in Sheet1!A1:An.

    Sub RenameSheets()
    Dim nxtName As Integer
     For nxtName = 1 To Sheets.Count - 1
      Sheets(nxtName + 1).Name = Sheets(1).Cells(nxtName, 1)
     Next
    End Sub

   

If the sheets are not in the same order as the list, then things get a bit more complicated. If each name appeared someplace in that person's sheet, then the code could search each sheet for the name and then rename that specific sheet to match the name it was searching for.

Before I offer any code to accomplish that, I'd need to know a little bit about your workbook, such as whether the names appear in the same cell (or a specific) in each sheet; does only that person's name appear in the sheet; etc.

...End quote from old post...

Seems to work for me but:
My problem is that my info is in different cells(C2,C15,C28 etc)All in C but every 13th cell(C2-C769)
C2 (Jack)
C15 (Mary)
C28 (Peter)
Hope this help.
Thanks in advance

Renier


See More: how to change sheet name in cell

Report •

#1
November 6, 2011 at 16:08:48
Untested...

Sub RenameSheets()
Dim nxtName As Integer
 ShtNum = 1
   For nxtName = 2 To 769 Step 13
    ShtNum = ShtNum + 1
     Sheets(ShtNum).Name = Sheets(1).Cells(nxtName, 1)
   Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
November 6, 2011 at 16:40:54
No can't seem to get it to work(newbie @ this)
This on the other hand work.

Private Sub Worksheet_Change(ByVal Target As Range) 
    Dim lCount As Long, rCell As Range 
    If Target.Cells.Count > 1 Then Exit Sub 
     
     
    On Error Resume Next 
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then 
         
        For Each rCell In Me.Range("A1:A10") 
            lCount = lCount + 1 
            If Sheets(lCount).Name <> Me.Name Then 
                Sheets(lCount).Name = Me.Cells(lCount, "A") 
            End If 
        Next rCell 
         
    End If 
     
     
End Sub 

Now to implement this in my situation.
Thanks for the help.

Renier


Report •

#3
November 6, 2011 at 16:53:18
Good luck getting that code to work...

This line can not be modified in such a way as to read data from every 13th row:

For Each rCell In Me.Range("A1:A10")

However, the method I suggested does just that:

For nxtName = 2 To 769 Step 13

Try this version, which I changed so that it reads Column C, not Column A...

Sub RenameSheets()
Dim nxtName As Integer
 ShtNum = 1
   For nxtName = 2 To 769 Step 13
    ShtNum = ShtNum + 1
     Sheets(ShtNum).Name = Sheets(1).Cells(nxtName, 3)
   Next
End Sub

BTW...this code assumes that all of the sheets already exist. It does not create sheets since the original post that you quoted contained code that assumed the sheets existed and you stated that it "Seems to work for me".

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
November 7, 2011 at 01:17:43
Thanks will have a go at it.

Renier


Report •

Ask Question