vba 从一个工作簿复制工作表并保存在一个新的工作簿中，名称为单元格中的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15360941/
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
Copy a sheet from one workbook and save in a new workbook with the name being a value in a cell
提问by Mike Barnes
I would like to copy Sheet2 from Workbook1 and paste it in Sheet1 of a new Workbook and save the new Workbook as the value of cell A1 in Sheet2, Workbook 1. I would like this macro to fire when I click a button that I have added.
我想从 Workbook1 复制 Sheet2 并将其粘贴到新工作簿的 Sheet1 中，并将新工作簿保存为 Sheet2, Workbook 1 中单元格 A1 的值。我希望在单击我添加的按钮时触发此宏.
Here is what I have done so far but it creates two Workbooks - One with the correct name but not the content of Sheet2, Workbook1 - And another that has the content of Sheet2, Workbook1 but not the correct Workbook name
这是我到目前为止所做的，但它创建了两个工作簿 - 一个具有正确的名称但不是 Sheet2, Workbook1 的内容 - 另一个具有 Sheet2, Workbook1 的内容但没有正确的工作簿名称
Sub CreateWorkbook()
Dim n As String
n = ThisWorkbook.Path & "\" & Range("A1").Value & ".xls"
Workbooks.Add
ActiveWorkbook.SaveAs n
ThisWorkbook.Sheets("Sheet2").Copy
ActiveWorkbook.Sheets("Sheet1").PasteSpecial xlPasteValues
End Sub
What am I missing here?
我在这里缺少什么？
回答by NickSlash
I don't think the new workbook created by Workbooks.Addbecomes ActiveWorkbookby default.
我不认为默认情况下创建的新工作簿Workbooks.Add会成为ActiveWorkbook。
Working with ThisWorkbookand ActiveWorkbookcan get confusing.
使用ThisWorkbook和ActiveWorkbook可能会令人困惑。
I like to set things like Workbooks and worksheets to variables so you know what your dealing with :)
我喜欢将工作簿和工作表之类的东西设置为变量，这样你就知道你在处理什么:)
I couldn't get the Copythen PasteSpecialbit to work (although I didn't try all that hard).
我无法让Copy当时的PasteSpecial位工作（虽然我没有那么努力）。
The example below should do what you want. I used Sheet2!A1for the file name, wasnt sure what the AK545in your code example was for.
下面的例子应该做你想要的。我用于Sheet2!A1文件名，不确定AK545您的代码示例中的内容是什么。
Sub Example()
Dim Output As Workbook
Dim FileName As String
Set Output = Workbooks.Add
FileName = ThisWorkbook.Path & "\" & ThisWorkbook.Worksheets(2).Cells(1, 1).Value & ".xls"
Output.SaveAs FileName
Application.DisplayAlerts = False
Output.Worksheets("Sheet1").Delete
ThisWorkbook.Worksheets(2).Copy Before:=Output.Worksheets("Sheet2")
Output.Worksheets(1).Name = "Sheet1"
Application.DisplayAlerts = True
Output.Save
End Sub
