Renaming worksheets using a cell references which can work with Mass update

[nikou]

Hi, can you please help me with this. thank you
I have 21 worksheets
I will refer to all my worksheets by codename
Cells A2 through A21 of Sheet1 contains a list of PO Numbers. I want worksheets 2 through 21 to be renamed based on sheet1 cells A2 through A21.
example :
if Sheet1, Cell A2=10-7879 then Shee2.name=10-7879
if Sheet1, Cell A3=10-5463 then Sheet3.name=10-5463
.... etc

I need the code to work on a mass update of cells A2 through A21. I found some code but they only work if I enter each cell one by one and not if i do a mass update.

 

[Mentor82]

Hi,
Try this and ket.me know if that's what you need.

Dim ws as worksheet
Dim rng as range
Dim rCell as range
Dim lngSheetNo&
Set ws = sheets(1)
Set rng=ws.range("A2:A21")

lngSheetNo=2
For each rCell in rng
    If rCell.value<>"" then
   Sheets(lngSheetNo).name=cstr(rCell.value)
    End if
    lngSheetNo=lngSheetNo+1
Next
Msgbox "Done"
Set rng=nothing
Set rCell= nothing
Set ws =nothing
End sub

[\CODE]

 

[nikou]

Thank you this works with some minor issues

I added your code plus the following header- is this correct?

Private Sub Worksheet_Change(ByVal Target As Range)

some minor issues:
issue 1- when I update cells A2:21, all the sheets 2 thorough 21 get renamed which is great, but if i try to change one of the cell values or delete the value, I get an error saying "run time error, this name is already taken". But the worksheet still updates to the new value . The value I entered is not a duplicate value.
Example if i change cell A3 from AAAAA to BBBB, Sheet3 does get renamed to BBBB but i still get a run time error.

issue 2- Once all the sheets are renamed. Any updates i make to sheet2 on any cell I either get this run time error or a msg box saying 'OK'. I thing the script is running every time i make an update to sheet2.

I am very new to VB scripting
thank you for your help

 

[Mentor82]

Hi,
I highly recomend you for massive update not to place the code I procided you with in Private Sub Worksheet_Change(ByVal Target As Range). This method runs the macro each time you change anything in the cell. I'd be better if you add this code like ex. below in the module, assign this to a button and run it whenever you need.

Sub WorksheetsNameUpdate()
Dim ws as worksheet
Dim rng as range
Dim rCell as range
Dim lngSheetNo&
Set ws = sheets(1)
Set rng=ws.range("A2:A21")

lngSheetNo=2
For each rCell in rng
If rCell.value<>"" then
Sheets(lngSheetNo).name=cstr(rCell.value)
End if
lngSheetNo=lngSheetNo+1
Next
Msgbox "Done"
Set rng=nothing
Set rCell= nothing
Set ws =nothing
End sub

However, if you do need to update a worksheet name when you make change in a cell maybe the following code would be ok for you. Place thw code in the sheet 1. Only those worksheers are updated of which related cells values are changed.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:A21")) Is Nothing Then
If target.value <>"" then
Sheets(target.row).name = cstr(target.value)
End if
End If
End Sub

 

[nikou]

Thank you. The second code is more what I am looking for. However it only works if I update one cell at a time. I can't do a mass update of A2:A21. I get a runtime error and the worksheets dont get renamed. thanks again for helping me out

 

[Mentor82]

Hi,
How do you a mass update in those cells? Do you update them with autofill-in, manually or by another macro, or another way?

 

[Mentor82]

Hi,
Try this.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell as range 
For each rCell in Target.cells
     If Not Intersect(rCell, Range("A2:A21")) Is Nothing then
          If rCell.value <>"" and Sheets(rCell.row).name <> cstr(rCell.value) then
               Sheets(rCell.row).name =cstr(rCell.value)
          End if
     End If
Next rCell
End Sub

 

[nikou]

This is perfect! thank you so much. I was doing mass update by using utofill-in, manually pasting values from A2:A21- No other macros are being used
the code you provided does exactly what i was looking for. Thank you so much !

 

[Mentor82]

I'm happy to help. Thanks a lot for your feedback