vba 如何将多张工作表复制到新工作簿？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27377513/
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
How to copy multiple sheets to a new workbook?
提问by Callum
I am trying to create a Save As function for a report.
我正在尝试为报告创建另存为函数。
The Master sheet has 25 tabs. I am looking to copy 23 of the 25 to a daily Save As to a specific folder.
主表有 25 个选项卡。我希望将 25 个中的 23 个复制到特定文件夹的每日另存为。
I have an Error Message warning box blocking users from saving the file so they are unable to mess with it.
我有一个错误消息警告框阻止用户保存文件，因此他们无法弄乱它。
Sub SaveMain()
Application.EnableEvents = False
'Stop ThisWorkbook VBA currently blocking the user being able to Save
Dim FlName As String
Dim FilePath As String
Dim NewWkBk As Workbook
Dim FileDate As String
FlName = " September Reporting" & InputBox("Enter Yesterday's Date DD/MM/YYYY:", "Creating New File...") & ".xlsb"
FilePath = "Z:\Call Agent Brief\Reporting\September Reporting\Reports"
Set NewWkBk = Workbooks.Add
Windows("September Reports Calculator - MASTER COPY.xlsb").Activate
Sheets(Array("Admin Tab", "Home Tab", "Dashboard", "Drop Down Values", _
"Reports Home", "Deployments", "Daily Summary", "Daily Breakdown", _
"Monthly Summary", "Monthly Breakdown - Title Page", "Monthly Breakdown", _
"Monthly Rolling 12 Months", "Monthly Cancellations", "Non-Deployments", _
"Non-Deployments Summary", "Non-Deployments Breakdown", "FNOL", "FNOL Summary", _
"FNOL Breakdown", "FNOL Deployments by User", "FNOL Deployments by Team", _
"FNOL Deployments by Insurer", "FNOL Non-Deployed Opportunities")).Copy After:=Workbooks(NewWkBk)
NewWkBk.SaveAs FilePath & "\" & FlName, FileFormat:=xlsb
Application.EnableEvents = True
End Sub
When running the macro the form for the date entry correctly loads, however a runtime error message appears.
运行宏时，日期条目的表单会正确加载，但会出现运行时错误消息。
Run-time error ‘13': Type mismatch
运行时错误“13”：类型不匹配
The debug highlights the long Sheets copyline. Even when I limit it to copying one tab it comes back with the same issue.
调试突出显示了长Sheets copy行。即使我将它限制为复制一个标签，它也会出现同样的问题。
I cannot Paste Values to a new sheet as there is a lot of formatting in the Master sheet designed to make this user friendly. As far as I can tell the only way is to make a copy of the entire sheet.
我无法将值粘贴到新工作表，因为主工作表中有很多格式旨在使此用户友好。据我所知，唯一的方法是制作整个工作表的副本。
回答by Trent MIller
You are going to have issues I think with what you are naming the file, starting with a space, using "/" in the name. Once you fix that I think this will work:
我认为您对文件的命名方式会有问题，以空格开头，在名称中使用“/”。一旦你解决了这个问题，我认为这会奏效：
Sub SaveMain()
Application.EnableEvents = False
'Stop ThisWorkbook VBA currently blocking the user being able to Save
Dim FlName As String
Dim FilePath As String
Dim NewWkBk As Workbook
Dim FileDate As String
FlName = "September Reporting" & InputBox("filename ", "Creating New File...") & ".xlsb"
FilePath = "C:\users\adm123\documents\xlworking"
Sheets(Array("Admin Tab", "Home Tab", "Dashboard", "Drop Down Values", _
"Reports Home", "Deployments", "Daily Summary", "Daily Breakdown", _
"Monthly Summary", "Monthly Breakdown - Title Page", "Monthly Breakdown", _
"Monthly Rolling 12 Months", "Monthly Cancellations", "Non-
Deployments", _
"Non-Deployments Summary", "Non-Deployments
Breakdown", "FNOL", "FNOL Summary", _
"FNOL Breakdown", "FNOL Deployments by User", "FNOL Deployments by
Team", _
"FNOL Deployments by Insurer", "FNOL Non-Deployed
Opportunities")).Copy
newfilename = FilePath & "\" & FlName
With ActiveWorkbook
.SaveAs newfilename, FileFormat:=50
.Close 0
End With
Application.EnableEvents = True
End Sub
