VBA Excel，根据单元格值重命名工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23644122/
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
VBA Excel, rename sheets base on Cell Value
提问by Joshua Tan
i need to rename all my sheets dynamically based on a range of cell values. This is my VBA codes, it keeps giving me a 'Runtime Error '1004 whenever i run it.
我需要根据一系列单元格值动态重命名我的所有工作表。这是我的 VBA 代码，每当我运行它时，它都会给我一个“运行时错误”1004。
Sub RenameSheets()
Dim MyCell As Range, MyRange As Range
Set MyRange = Sheets("Config").Range("A5")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
Sheets("Sheet1").Activate
For Each MyCell In MyRange
ActiveSheet.Name = MyCell.Value 'Error here. it works fine if i rename MyCell.Value to "AnyRandomValue"
Worksheets(ActiveSheet.Index + 1).Select
Next MyCell
End Sub
I cant get my head around it. Why is it giving an error at MyCell.Value? Please help!
我无法理解它。为什么它在 MyCell.Value 上给出错误？请帮忙！
回答by L42
The problem I think is activating the sheets your working on by .Selectmethod.
Avoid using selectas much as possible. Check out the link.
Your code can be rewritten like below:
我认为的问题是通过.Select方法激活您正在处理的工作表。
尽量避免使用 select。查看链接。
您的代码可以像下面这样重写：
Sub RenameSheets()
Dim MyNames As Variant
MyRange As Range, ws As Worksheet
Dim i As Long
With Sheets("Config")
Set MyRange = .Range("A5", .Range("A" & _
.Rows.Count).End(xlUp).Address)
'~~> pass names to array
MyNames = Application.Transpose(MyRange)
i = Lbound(MyNames)
End With
'~~> iterate sheets instead
For Each ws In Worksheets
If ws.Name <> "Config" Then
ws.Name = MyNames(i) '~~> retrieve from array
i = i + 1
End If
Next ws
End Sub
Is this what you're trying?
这是你正在尝试的吗？
