如果数组中的工作表不存在，则忽略错误的 VBA 代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14516005/
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 code to ignore error if sheet from array not exist
提问by Prash
I am trying to combine specific sheets to one sheet from workbook. Challenge here is sheets from array might not be available all the time. so the macro should ignore those and move to next sheet to copy data. I have written code but macro throes error when sheet does not exist.
我正在尝试将特定工作表合并到工作簿中的一张工作表中。这里的挑战是数组中的工作表可能并非一直可用。所以宏应该忽略这些并移动到下一张表以复制数据。我已经编写了代码，但是当工作表不存在时，宏会出错。
Sub test()
Dim MyArr, j As Long
Dim ws As Worksheet
Dim sary, i As Long
Worksheets.Add Before:=Worksheets("Equity")
ActiveSheet.Name = "Consolidated"
MyArr = Array("Sample Sheet_Equity", "Sample Sheet_Funds", "Sample Sheet_Warrants", "Eq", "Fu", "Wa")
For j = 0 To UBound(MyArr)
Set ws = Worksheets(MyArr(j))
If Not ws Is Nothing Then
ws.Select
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Consolidated").Select
Range("A2").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
End If
Next
End Sub
回答by peterm
You can do it like this:
你可以这样做：
For j = 0 To UBound(MyArr)
On Error Resume Next
Set ws = Worksheets(MyArr(j))
If Err.Number = 0 Then
On Error GoTo 0
If Not ws Is Nothing Then
'Your copying code goes here
End If
Else
Err.Clear
End If
Next
UPDATE:Thanks to Doug Glancy's comment here is more streamlined version
更新：感谢道格格兰西的评论，这里是更精简的版本
For j = 0 To UBound(MyArr)
Set ws = Nothing
On Error Resume Next
Set ws = Worksheets(MyArr(j))
On Error GoTo 0
If Not ws Is Nothing Then
'Your copying code goes here
End If
Next
