使用 VBA 从列表中重命名多个工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21052202/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
renaming multiple worksheets from list using VBA
提问by user3066783
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?
我是 VBA 新手，正在尝试从列表中命名工作表。我有一个包含 133 个工作表的文件，我想从其中一个工作表的列表中命名每个工作表。单元格 B1 到 B133 具有所需名称的列表，单元格 c1 至 c133 具有工作表名称（工作表 1 至工作表 133）。我尝试了 2 种不同的代码无济于事。我错过了什么？
Here's an excerpt of what columns B & C look like.
以下是 B 和 C 列外观的摘录。
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')
#1（运行时错误“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')
#2（运行时错误“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
回答by David
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
回答by Dmitry Pavliv
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:C133range should contain sheet names without quotes (correct: Sheet1, uncorrect: "Sheet1")
请注意，你的C1:C133范围应包含表名称不带引号（正确的：Sheet1，uncorrect： "Sheet1"）
回答by M.Banerjee
Probably the thing that's throwing the error is Sheetz. The index of the sheet should be in parentheses i.e. Sheet(z)
可能引发错误的事情是Sheetz. 工作表的索引应在括号中，即Sheet(z)
