Excel 中的 VBA 宏 If, Then, Else？

萨拉希尔

我有 3 个版本的工作簿，1 个有 4 个选项卡，一个有 8 个，一个有 12 个。最后一个中的所有选项卡都包含其他两个工作簿，第二个相同的包含第一个。我需要的是让我的宏跳过在早期版本中搜索其他 8/4 选项卡，这样我就不必在每次加载旧版本的工作簿时单击调试。我对此很陌生，所以任何帮助都会很棒。先感谢您。

发生错误是因为该选项卡在某些版本的工作簿中不存在。

Sub TO_LOAD_OctDec()
' Macro to load data from workbook to master workbook.
'

' Open master database and prepare for transfer
    Workbooks.Open Filename:="S:\Property & Casualty\PPE\Wildfires\California Wildfires 2017\Submissions\CWF2017-MasterDatabase.xlsx"


' Focus is given to workbook to obtain correct filename.
    ActiveWindow.ActivatePrevious
    Sheets("Ready").Select
    Range("A9").Select
    Selection.ClearContents

' Transfer company info
    Application.GoTo Reference:="CoInfo"
    Selection.Copy
    ActiveWindow.ActivateNext
    Sheets("CoInfo").Select
    Range("A1").Select
    ActiveCell.SpecialCells(xlLastCell).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.End(xlToLeft).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("A1").Select

' Transfer personal property data
    ActiveWindow.ActivatePrevious
    Range("PersonalP").Select
    Selection.Copy
    ActiveWindow.ActivateNext
    Sheets("Data").Select
    Range("A1").Select
    ActiveCell.SpecialCells(xlLastCell).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.End(xlToLeft).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("A1").Select

' Transfer commercial property data
    ActiveWindow.ActivatePrevious
    Range("CommercialP").Select
    Selection.Copy
    ActiveWindow.ActivateNext
    Sheets("Data").Select
    Range("A1").Select
    ActiveCell.SpecialCells(xlLastCell).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.End(xlToLeft).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("A1").Select

' Transfer Auto data
    ActiveWindow.ActivatePrevious
    Range("Auto").Select
    Selection.Copy
    ActiveWindow.ActivateNext
    Sheets("Data").Select
    Range("A1").Select
    ActiveCell.SpecialCells(xlLastCell).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.End(xlToLeft).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("A1").Select

' Transfer Other Lines data
    ActiveWindow.ActivatePrevious
    Range("OtherLines").Select
    Selection.Copy
    ActiveWindow.ActivateNext
    Sheets("Data").Select
    Range("A1").Select
    ActiveCell.SpecialCells(xlLastCell).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.End(xlToLeft).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("A1").Select

' Transfer DF personal property data
    ActiveWindow.ActivatePrevious
    Range("DF_Residential").Select
    Selection.Copy
    ActiveWindow.ActivateNext
    Sheets("Data").Select
    Range("A1").Select
    ActiveCell.SpecialCells(xlLastCell).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.End(xlToLeft).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("A1").Select

' Transfer DF commercial property data
    ActiveWindow.ActivatePrevious
    Range("DF_Commercial").Select
    Selection.Copy
    ActiveWindow.ActivateNext
    Sheets("Data").Select
    Range("A1").Select
    ActiveCell.SpecialCells(xlLastCell).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.End(xlToLeft).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("A1").Select

' Transfer DF Auto data
    ActiveWindow.ActivatePrevious
    Range("DF_Auto").Select
    Selection.Copy
    ActiveWindow.ActivateNext
    Sheets("Data").Select
    Range("A1").Select
    ActiveCell.SpecialCells(xlLastCell).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.End(xlToLeft).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("A1").Select

' Transfer DF Other Lines data
    ActiveWindow.ActivatePrevious
    Range("DF_Other").Select
    Selection.Copy
    ActiveWindow.ActivateNext
    Sheets("Data").Select
    Range("A1").Select
    ActiveCell.SpecialCells(xlLastCell).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.End(xlToLeft).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("A1").Select

' Transfer MM Personal Property data
    ActiveWindow.ActivatePrevious
    Range("MM_Personal").Select
    Selection.Copy
    ActiveWindow.ActivateNext
    Sheets("Data").Select
    Range("A1").Select
    ActiveCell.SpecialCells(xlLastCell).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.End(xlToLeft).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("A1").Select

' Transfer MM Commercial Property data
    ActiveWindow.ActivatePrevious
    Range("MM_Commercial").Select
    Selection.Copy
    ActiveWindow.ActivateNext
    Sheets("Data").Select
    Range("A1").Select
    ActiveCell.SpecialCells(xlLastCell).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.End(xlToLeft).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("A1").Select

' Transfer MM Auto data
    ActiveWindow.ActivatePrevious
    Range("MM_Auto").Select
    Selection.Copy
    ActiveWindow.ActivateNext
    Sheets("Data").Select
    Range("A1").Select
    ActiveCell.SpecialCells(xlLastCell).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.End(xlToLeft).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("A1").Select

' Transfer MM Other Lines data
    ActiveWindow.ActivatePrevious
    Range("MM_Other").Select
    Selection.Copy
    ActiveWindow.ActivateNext
    Sheets("Data").Select
    Range("A1").Select
    ActiveCell.SpecialCells(xlLastCell).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.End(xlToLeft).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("A1").Select



' Save and close master database
    Sheets("CoInfo").Select
    ActiveWorkbook.Save
    ActiveWorkbook.Close

' TimeStamp
    ActiveWindow.ActivateNext
    Range("F11").Select
    ActiveCell.FormulaR1C1 = "=NOW()"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    ActiveWorkbook.Close

End Sub

斯卡

这是一个用于确定工作表是否存在的函数：

Public Function HasSheet(SheetName As String) As Boolean
    Dim i As Integer
    Dim sheetNameUcase As String
    sheetNameUcase = UCase(SheetName)
    For i = 1 To Sheets.Count
        If UCase(Sheets.Item(i).Name) = sheetNameUcase Then
            HasSheet = True
            Exit Function
        End If
    Next
    HasSheet = False
End Function

然后像在这个例子中一样使用它：

' Transfer DF Auto data
If HasSheet("Data") Then
    ActiveWindow.ActivatePrevious
    Range("DF_Auto").Select
    Selection.Copy
    ActiveWindow.ActivateNext
    Sheets("Data").Select
    Range("A1").Select
    ActiveCell.SpecialCells(xlLastCell).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.End(xlToLeft).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("A1").Select
End If

另外，避免使用On Error Resume Next. 从本质上讲，它禁用了所有错误捕获。应该像对待原子弹一样对待它。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。