nameing tabs based on cells

Smithgall

Board Regular
Joined
May 24, 2006
Messages
68
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?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
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

Board Regular
Joined
May 24, 2006
Messages
68
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

Board Regular
Joined
May 24, 2006
Messages
68
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

Well-known Member
Joined
Dec 15, 2005
Messages
818
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)

Code:
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
 

Forum statistics

Threads
1,136,272
Messages
5,674,757
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top