vba Excel 添加工作表或覆盖（如果存在）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28547879/
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
Excel Add Sheet or Overwrite if Exists
提问by Bigotacon
In excel I have a macro that converts all of columns of an active sheet into an new sheet called "MasterList"
在 excel 中，我有一个宏可以将活动工作表的所有列转换为名为“MasterList”的新工作表
My problem is when I rerun that macro I get an error saying "That name is already taken." Try a different one.
我的问题是，当我重新运行该宏时，我收到一条错误消息，提示“该名称已被占用”。尝试不同的。
I need my macro to overwrite MaterList sheet if it already exists.
如果已经存在，我需要我的宏来覆盖 MaterList 表。
Here is my code:
这是我的代码：
Sub ToArrayAndBack()
Dim arr As Variant, lLoop1 As Long, lLoop2 As Long
Dim arr2 As Variant, lIndex As Long
'turn off updates to speed up code execution
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
End With
ReDim arr2(ActiveSheet.UsedRange.Cells.Count - ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Count)
arr = ActiveSheet.UsedRange.Value
For lLoop1 = LBound(arr, 1) To UBound(arr, 1)
For lLoop2 = LBound(arr, 2) To UBound(arr, 2)
If Len(Trim(arr(lLoop1, lLoop2))) > 0 Then
arr2(lIndex) = arr(lLoop1, lLoop2)
lIndex = lIndex + 1
End If
Next
Next
Sheets.Add.Name = "MasterList"
Range("A1").Resize(, lIndex + 1).Value = arr2
Range("A1").Resize(, lIndex + 1).Copy
Range("A2").Resize(lIndex + 1).PasteSpecial Transpose:=True
Rows(1).Delete
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
End With
End Sub
采纳答案by Tarik
You can put the sheet creation between on error resume and on error goto 0. The other solution is to loop through the workbook sheets collection and check if a sheet with that name exists.
您可以将工作表创建放在错误恢复和错误转到 0 之间。另一种解决方案是遍历工作簿工作表集合并检查是否存在具有该名称的工作表。
Solution 1:
解决方案1：
On Error Resume Next
Sheets.Add.Name = "MasterList"
On Error GoTo 0
Solution 2:
解决方案2：
Dim ws As Worksheet
Dim found As Boolean
found = False
For Each ws In ThisWorkbook.Sheets
If ws.Name = "MasterList" Then
found = True
Exit For
EndIf
Next
If Not found Then
Sheets.Add.Name = "MasterList"
EndIf
To avoid relying on the fact that MasterList is active:
为避免依赖 MasterList 处于活动状态的事实：
Set ws = ThisWorkbook.Sheets("MasterList")
With ws
.Range("A1").Resize(, lIndex + 1).Value = arr2
.Range("A1").Resize(, lIndex + 1).Copy
.Range("A2").Resize(lIndex + 1).PasteSpecial Transpose:=True
.Rows(1).Delete
End With
