renaming multiple worksheets from list using VBA

Question

I'm new to VBA and am trying to name worksheets from a list. I have a file with 133 worksheets and want to name each sheet from a list in one of the worksheets. Cells B1 thru B133 has a list of the desired names, cells c1 through c133 has sheet names (Sheet1 through Sheet 133). I've tried 2 different codes to no avail. What am I missing?

Here's an excerpt of what columns B & C look like.

File details                    Sheet 1
Sheet Names                     Sheet 23
Calc Notes                      Sheet 2
Rank comparison - baseline      Sheet 3
Trend - Top 30 ct vs baseline   Sheet 5
Trend - Top 30 dur vs baseline  Sheet 6
Trend - Top 30 MTBF vs baseline Sheet 7
Trend - Top 30 ct_dur vs base   Sheet 8
Avail, MTBeF, MTTR scorecard    Sheet 10
Avail, MTBeF, MTTR - Excluded   Sheet 11
All-in vs Excluded              Sheet 12
Summary all lines - count            Sheet 13
Summary all lines - duration    Sheet 14
fault - count                   Sheet 15
fault - duration                Sheet 16
gap count-query vs fault sum    Sheet 17
gap duration-query vs fault sum Sheet 18
missing faults                  Sheet 20
query                           Sheet 9
Prod unit ref                   Sheet 21
Pd Wk ref                       Sheet 22
Query ref                       Sheet 4
FTT                             Sheet 19

#1 (runtime error '424')

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim x As Integer
Dim y As Integer
Dim z As Integer
x = 1
y = 133

For z = 1 To 133
sheetz.Name = Range(Cells(x, 2), Cells(y, 2))
Next z


End Sub

#2 (runtime error '91')

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ws As Excel.Worksheet
Dim z As Integer

    For z = 1 To 133
        If ws.Name = Sheetz Then
            Sheetz.Name = Cells(z, 2)
        End If
        Exit For
    Next z

End Sub

Answer 1

Sub RenameSheets()
    For i = 1 To 133
        On Error Resume Next
        oldname = Cells(i, 3).Value
        newname = Cells(i, 2).Value
        Sheets(oldname).Name = newname
    Next
End Sub

Answer 2

Probably the thing that's throwing the error is Sheetz. The index of the sheet should be in parentheses i.e. Sheet(z)

Answer 3

Try this code instead:

Sub test()
   On Error Resume Next
   For Each oldName In ThisWorkbook.Workseets("Sheet1").Range("C1:C133")
       ThisWorkbook.Worksheets(oldName.Value).Name = oldName.Offset(0, -1).Value
   Next
End Sub

Note, that your C1:C133 range should contain sheet names without quotes (correct: Sheet1, uncorrect: "Sheet1")