Help needed with macro to rename worksheets in workbook.

[Chris Mitchell]

I have a workbook that contains 29 sheets.



I want to rename sheets 3 to 27, with names from specified cells on sheet
29.



The names will be made from the concatenation of the contents of 2 cells on
sheet 1.



I got a macro from this NG some time ago that used to do the job, but the
workbook has changed substantially since then with new sheets having been
added and others deleted.



When I list the Macros I have 2; Sheet35.fid and Sheet35.listsheets. These
are both the same as follows:



Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row < 3 Or Target.Column <> 2 Then Exit Sub

On Error Resume Next

Application.EnableEvents = False

Sheets(Target.Row).Name = Target

Application.EnableEvents = True

End Sub

Sub fid()

Application.EnableEvents = True

End Sub

Sub listsheets()

Range("b3:b" & Cells(Rows.Count, "b").End(xlUp).Row).Clear

For i = 1 To Worksheets.Count

'MsgBox Sheets(i).Name

If Sheets(i).Name <> "TEMPLATE" And _

Sheets(i).Name <> "VALIDATION" And _

Sheets(i).Name <> "Summary" And _

Sheets(i).Name <> "DataEntry" Then

Cells(i + 0, "b") = Sheets(i).Name

End If

Next i

End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

Application.DisplayAlerts = False

Dim WantedSheet As String

WantedSheet = Trim(ActiveCell.Value)

If WantedSheet = "" Then Exit Sub

On Error Resume Next

If Sheets(ActiveCell.Value) Is Nothing Then

'GetWorkbook ' calls another macro to do that

Else

Sheets(ActiveCell.Value).Select

ActiveSheet.Range("a11").Select

End If

Application.DisplayAlerts = True

End Sub



This works for the first sheet to be renamed, but not any others.



When I step into it 'Sub listsheets()' is highlighted.



I don't understand macros, yet, so don't know if this is broken in some way.



Can anyone see anything wrong with this macro, or suggest another macro or a
better non-macro way of achieving this?



TIA.



Chris.

 

[Bob Phillips]

All you seem to need is

Sub NameSheets()

For i = 1 To Worksheets.Count

If Sheets(i).Name <> "TEMPLATE" And _
Sheets(i).Name <> "VALIDATION" And _
Sheets(i).Name <> "Summary" And _
Sheets(i).Name <> "DataEntry" Then

Sheets(i).Name = Sheets(1).Cells(i, "b") & Sheets(1).Cells(i,
"C")
End If
Next i

End Sub

assuming the value comes from B and C


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Chris Mitchell]

Thanks Bob.

As I said I don't know much about macros, but I don't see how this only
affects sheets 3 to 27 (should have been 25) only out of 29.

Also I don't see how this steps down the list of names for successive
sheets.

Also if I've already done the concatenation on another sheet presumably

Sheets(i).Name = Sheets(1).Cells(i, "b") & Sheets(1).Cells(i, "C")

becomes

Sheets(i).Name = Sheets(1).Cells(i, "b")

Where b equals the cell ref containing the first sheet name, e.g. C3

The next sheet name is in Sheets(1).CellsC4

However I've probably missed something, any further clarification would be
appreciated.

TIA.

Chris.