nameing tabs based on cells

[Smithgall]

i am using the following code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
If Not Intersect(Sh.Range("A1"), Target) Is Nothing Then Sh.Name = Target.Value
End Sub

It works fine but my problem is that each tab has A1 filled as a reference back to the "NAMES" sheet (=Names!B3). Meaning that names tab has a list of 1-x each with a corresponding name. then i have a sheet for each name. The above code works when i go to each sheet and type in the name in A1 but it does not work when the "Names" sheet is updated and therefore updates its corresponding sheet name. Any suggestions?

 

[lozzablake]

Do you want the NAMES sheet not to be dynamically updated? If so then just add an extra condition to the IF statement. eg

and sh.name<>"NAMES"

 

[Smithgall]

Dynamically update sheet names

I think i misunderstand your comment. Yes I want the pages to dynamically update. I have sheet 1 (names) that contains the names that change periodicaly. Then i have 1 sheet for each of those names. What i want to do is go to sheet1 (names) and fill in A1,A2,A3...with Bob, Jane, Mike..etc.

Since right now the number of sheets is fixed i have coded each one to have have its label in A1 pull the name from either A1, A2, A3 etc.

What is happening is the code i am using does not seem to work on the referenced cells. If i type "Bob" in A1 on any given sheet it will copy that text to the tab. What it will not do see A1 as this reference (=names!A2) and then pull the value.

does this make it more clear?

 

[Smithgall]

simple code

This code seems to work equally well i just do not know how to make one button control the whole worksheet. To work around i can put the same form buttion on each worksheet but that is obviously a long way to go. I would either like it to work dynamically so that everytime the name is changed it automatically updates or at a minimum use a button so that when they change names they can hit a button and the appropriate sheets are updated.

ActiveSheet.Name = Range("A1")

 

[lozzablake]

To clarify:

1) You type the names you want for each sheet into the NAMES sheet (cells A1..An)
2) Each sheet has a reference to one of these cells (eg sheet 1.range(a1) refers to Names range(a1),sheet 2.range(a1) refers to Names range(a2))
3) The sheet names then need to be updated by the appropriate names.

If this is the case why can't you just loop through the names on the names sheet and re-name the sheets appropriately? You could use a change event macro for this or apply it to a button on the names sheet

It would be something like this (change event to be pasted in the NAMES sheet module)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim intLast As Integer
    Dim i As Integer
    Dim arryNames()
    Dim ws As Worksheet
    
    intLast = Sheets("NAMES").Cells(65536, 1).End(xlUp).Row
    i = 1
    
    'loop through each worksheet
    For Each ws In ThisWorkbook.Sheets
        'check if the change was in the Names area and not on sheet NAMES
        If Not Application.Intersect(Target, Range(ActiveSheet.Cells(1, 1), Cells(intLast, 1))) Is Nothing And ws.Name <> "NAMES" Then
             ws.Name = Sheets("NAMES").Cells(i, 1)
             i = i + 1
        End If
    Next ws
        
End Sub