Macro/VBA Help

[charpentiert]

We have a workbook that saved as into new workbooks. For each workbook we have to take the existing tab name and replace the number in front with correct number. For instance below I recorded a macro to change "10002" to "100005" on all the tabs. Normally this is easy enough to do by hand but there are 44 workbooks with 6 tabs each.

The macro below doesn't work and I am not familiar enough to fix it. Please help.


Sub change()
'
' change Macro
'

'
Range("A1").Select
ActiveCell.FormulaR1C1 = "100005"
Range("F11").Select
Sheets("10002 a").Select
Sheets("10002 a").Name = "100005 a"
Range("C26").Select
Sheets("10002 b").Select
Sheets("10002 b").Name = "100005 b"
Sheets("10002 c").Select
Sheets("10002 c").Name = "100005 c"
Range("F23").Select
End Sub

 

[Joe4]

Try this:

Sub ReplaceNames()

    Dim ws As Worksheet
    Dim oldName As String
    Dim newName As String
    
    For Each ws In Worksheets
        oldName = ws.Name
        newName = Replace(oldName, "100002", "100005")
        If oldName <> newName Then
            Sheets(oldName).Name = newName
        End If
    Next ws
        
End Sub

 

[JLGWhiz]

Another method

Sub reNameSheets()
Dim newName As String, sh As Worksheet
newName = InputBox("Enter the new number for the sheet name", "NEW NUMBER FOR SHEET NAMES")
 For Each sh In ActiveWorkbook.Sheets
  sh.Name = newName & " " & Right(sh.Name, 1)
 Next
End Sub

 

[charpentiert]

What about if I wanted to select a list and then rename sheets based on that list?

 

[Joe4]

Do you mean like you have two columns, where one is the current sheet name, and one is the new sheet name?

 

[Joe4]

If my assumption is correct, you can do it like this:

Sub ReplaceNames2()

    Dim myRange As Range
    Dim cell As Range
    Dim oldName As String
    Dim newName As String
    
'   Set first column of range to look through
    Set myRange = Sheets("Sheet1").Range("A1:A3")
    
    For Each cell In myRange
        oldName = cell
        newName = cell.Offset(0, 1)
        Sheets(oldName).Name = newName
    Next cell
        
End Sub