将部分名称相似的选项卡复制到另一个工作簿

Question

如果有任何帮助，我们将不胜感激。比方说，我有以下选项卡：Data Set 001、Data Set 002、Data Set 003，只要选项卡名称包含数据集，它就应该复制到另一个工作簿(比方说Main File(。欢迎在这方面提供任何帮助。提前感谢大家！

谨致问候，Josh

Answer 1

我在包含工作表"评估表格xx"的工作簿中使用了这个：

Sub CopyWorksheetsToNewWorkbook()
'This macro is to be in the ActiveWorkbook
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks.Add
wb.SaveAs Filename:="Book10" & ".xlsx"
Workbooks.Open ("Book10.xlsx")
For Each ws In Workbooks("ActiveWorkbookName.xlsm").Sheets
If ws.Name Like ("ASSESSMENT FORM*") Then ws.Copy Before:=Workbooks("Book10.xlsx").Worksheets("Sheet1")
Next ws
Workbooks("Book10.xlsx").Worksheets("Sheet1").Move Before:=Workbooks("Book10.xlsx").Sheets(1)
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

请注意，带有工作表"ASSESSMENT FORMxx"的工作簿和Book10.xlsx都必须打开。

Answer 2

您尝试过任何代码吗？

Sub Whatever()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks("AnyOpenWorkbookName.xlsx")
For Each ws In ThisWorkbook.Sheets
If ws.Name Like "Data Set" & "*" Then
Call ws.Copy(after:=wb.Sheets(wb.Sheets.Count))
End If
Next ws
End Sub

Answer 3

您需要循环浏览第一个工作簿的所有工作表，用Worksheet.name和InStr.测试其名称是否包含数据集

如果InStr函数返回的值不是0(意味着您的工作表名称包含数据集(，则可以将当前工作表复制到第二个工作簿。

根据您的需求调整此样本：

'Loop through all worksheets
If InStr(wsCurrent.Name,"Data Set") <> 0 Then
' Copy wsCurrent to new WorkBook
End If
' End of loop