我在这里和其他论坛上尝试了很多不同的东西，但无法让我的代码正常工作。
我希望代码简单地跳过试图从"；管理者"；选项卡(如果该文件中没有"管理者"选项卡(。然后，我需要它来关闭文件并移动到列表中的下一个
主文件有两个选项卡，我希望将相关选项卡中的所有数据复制到其中。每个单独的文件也有相同的选项卡，但只包含它们的位置数据。
如果我使用"；On Error GoTo Skip"；如果我只选择没有第二个选项卡的工作簿，但当选择完整列表(大约42(时，我会得到运行时错误9，那么它就起作用了。我认为我需要加入一个检查，以确认工作表是否存在——可能使用了if Else，但无法使其发挥作用。
这是代码：
Sub Merge()
'Facilitates selecting the applicable files to be consolidated.
Dim TargetFiles As FileDialog
'Allows the macro to loop through the files once they have been selected.
Dim FileIdx As Long
'Sets variables so that each file can be accessed once selected.
Dim Databook As Workbook
'Speeds up the macro.
Application.DisplayAlerts = False
Application.Calculation = xlManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.AskToUpdateLinks = False
Application.EnableEvents = False
'Opens up File Explorer so that all relevant files can be selected.
Set TargetFiles = Application.FileDialog(msoFileDialogOpen)
With TargetFiles
.AllowMultiSelect = True
.Title = "Select all files."
.ButtonName = ""
.Filters.Clear
.Filters.Add ".xls* files", "*.xls*"
.Show
End With
'Loop through all files.
For FileIdx = 1 To TargetFiles.SelectedItems.Count
'Sets the workbook so that we can loop through.
Set Databook = Workbooks.Open(TargetFiles.SelectedItems(FileIdx))
'Copies the relevant data from the specified section.
Databook.Worksheets("Staff").Unprotect
Databook.Worksheets("Staff").AutoFilterMode = False
Databook.Worksheets("Staff").Range("A3:O1000").Copy
'Pastes it into the relevant area in the Database file.
Workbooks("Master Review - Test File").Sheets("Staff").Activate
'Macro seems to paste over the top rather than at the bottom if you don't activate A1 first.
Range("A1").Activate
Range("A100000").End(xlUp).Offset(1, 0).Activate
'Increase the 100000 above if your data is more than 100000 rows.
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Copies the relevant data from the specified section.
Databook.Worksheets("Managers").Unprotect
Databook.Worksheets("Managers").AutoFilterMode = False
Databook.Worksheets("Managers").Range("A3:P5000").Copy
'Pastes it into the relevant area in the Database file.
Workbooks("Master Review - Test File").Sheets("Managers").Activate
'Macro seems to paste over the top rather than at the bottom if you don't activate A1 first.
Range("A1").Activate
Range("A100000").End(xlUp).Offset(1, 0).Activate
'Increase the 100000 above if your data is more than 100000 rows.
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Closes the current workbook and moves onto the next one which has been selected
Databook.Close False
'Clears the clipboard at the end of each workbook being copied.
Application.CutCopyMode = False
Next
'Reset back to normal.
Application.DisplayAlerts = True
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.AskToUpdateLinks = True
Application.EnableEvents = True
End Sub
循环浏览图纸并选择要使用If ws.name =复制的图纸
Option Explicit
Sub Merge()
'Facilitates selecting the applicable files to be consolidated.
Dim TargetFiles As FileDialog
'Allows the macro to loop through the files once they have been selected.
Dim FileIdx As Long
'Sets variables so that each file can be accessed once selected.
Dim Databook As Workbook
'Speeds up the macro.
With Application
.DisplayAlerts = False
.Calculation = xlManual
.ScreenUpdating = False
.DisplayStatusBar = False
.AskToUpdateLinks = False
.EnableEvents = False
End With
'Opens up File Explorer so that all relevant files can be selected.
Set TargetFiles = Application.FileDialog(msoFileDialogOpen)
With TargetFiles
.AllowMultiSelect = True
.Title = "Select all files."
.ButtonName = ""
.Filters.Clear
.Filters.Add ".xls* files", "*.xls*"
.Show
End With
'Loop through all files.
Dim ws As Worksheet, wb As Workbook
Set wb = Workbooks("Master Review - Test File")
For FileIdx = 1 To TargetFiles.SelectedItems.Count
'Sets the workbook so that we can loop through.
Set Databook = Workbooks.Open(TargetFiles.SelectedItems(FileIdx))
For Each ws In Databook.Sheets
If ws.Name = "Staff" Then
'Copies the relevant data from the specified section.
ws.Unprotect
ws.AutoFilterMode = False
ws.Range("A3:O1000").Copy
'Pastes it into the relevant area in the Database file.
wb.Sheets("Staff").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ElseIf ws.Name = "Managers" Then
'Copies the relevant data from the specified section.
ws.Unprotect
ws.AutoFilterMode = False
ws.Range("A3:P5000").Copy
'Pastes it into the relevant area in the Database file.
wb.Sheets("Managers").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End If
Next
'Closes the current workbook and moves onto the next one which has been selected
Databook.Close False
'Clears the clipboard at the end of each workbook being copied.
Application.CutCopyMode = False
Next
'Reset back to normal.
With Application
.DisplayAlerts = True
.Calculation = xlAutomatic
.ScreenUpdating = True
.DisplayStatusBar = True
.AskToUpdateLinks = True
.EnableEvents = True
End With
End Sub