Excel 2003 VBA：将工作表移动到由变量引用的新工作簿中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3332941/
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
Excel 2003 VBA: Move a sheet into a new workbook that is referenced by a variable
提问by Margaret
I have a function that is meant to run the ShowPages()command of a PivotTable and then save each sheet to a separate file.
我有一个函数用于运行ShowPages()数据透视表的命令，然后将每个工作表保存到一个单独的文件中。
Here's how I wish I could do it:
这是我希望我能做到的方式：
Sub Split()
ThisWorkbook.Sheets("Data").PivotTables("Data").ShowPages PageField:="Codename"
Dim newWb As Workbook
For Each s In ThisWorkbook.Sheets
If s.Name <> "Data" Then
Set newWb = s.Move #This is the line I'm trying to work out
newWb.SaveAs Filename:="C:\Export\" + s.Name + ".xls"
newWb.Close
End If
Next s
End Sub
Unfortunately, this is running into a bunch of issues to do with not having created objects and suchlike (understandably). What is the most sensible way to do this?
不幸的是，这遇到了一堆与没有创建对象等有关的问题（可以理解）。这样做的最明智的方法是什么？
回答by soldieraman
Sub Split()
ThisWorkbook.Sheets("Data").PivotTables("Data").ShowPages PageField:="Codename"
Dim newWb As Workbook
For Each s In ThisWorkbook.Sheets
If s.Name <> "Data" Then
''Added by Soldieraman
Dim sheetName As String
sheetName = s.Name
Set newWb = Workbooks.Add
s.Move before:=newWb.Sheets(1)
Application.DisplayAlerts = False
newWb.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Delete
Application.DisplayAlerts = True
''Edited by soldieraman
newWb.SaveAs Filename:="C:\Export\Test" & sheetName & ".xls"
newWb.Close
End If
Next s
End Sub
回答by Mark Goldfain
Although this is old, and the accepted answer by soldieraman is very nice, just wanted to add one thing. The Excel VBA Sheets.Copy and Sheets.Move methods have a very nice feature. They take either of two optional arguments, "Before" or "After", to position a moved/copied sheet. The Excel documentation notes that:
虽然这是旧的，并且soldieraman接受的答案非常好，只是想补充一件事。Excel VBA Sheets.Copy 和 Sheets.Move 方法有一个非常好的功能。它们采用两个可选参数“之前”或“之后”中的任意一个来定位移动/复制的工作表。Excel 文档指出：
If you don't specify either Before or After, Microsoft Excel
creates a new workbook that contains the moved [copied] sheet.
So, it is almost surprising, but you can just say:
所以，这几乎令人惊讶，但你可以说：
Sheets(sheetname).Move
in the accepted answer, in place of:
在接受的答案中，代替：
Set newWb = Workbooks.Add
s.Move before:=newWb.Sheets(1)
Application.DisplayAlerts = False
newWb.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Delete
Application.DisplayAlerts = True
The rest of soldieraman's code would work fine with this simplification.
士兵的其余代码可以在这种简化下正常工作。
