changing sheet names

[Andy Healey]

Hi all

I have a list of names in a range "A2:A65" on a sheet called ops names.

In the same workbook I have a sheet for each different name, (sheets2 to 65)
What I would like to do is to rename the sheets with the names in the range.

So the name in cell A2 becomes the sheet name of sheet 2, and so on and so
on.

The spreadsheet is used for time sheets, but the staff are not always the
same each month, hence the reason the change sheet names.

Any help greatly appreciated.


best regards

Andy

 

[Bob Phillips]

Andy,

Simple loop required

For i = 2 To ActiveWorkbook.Worksheets.Count
Worksheets(i).Name = Worksheets("ops names").Cells(i, "A").Value
Next

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ron de Bruin]

Hi Andy

Try this with the sheet names as first

Sub test()
For a = 2 To ThisWorkbook.Sheets.Count
On Error Resume Next
Sheets(a).Name = Sheets("names").Cells(a, "A").Value
On Error GoTo 0
Next
End Sub