vba 如何将工作表保存到新工作簿？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45334469/
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 do I save a worksheet to a new workbook?
提问by MrDoe
I am trying to save a single worksheet to a new workbook (VBA should create a new workbook).
我正在尝试将单个工作表保存到新工作簿（VBA 应该创建一个新工作簿）。
The code is running and the new workbook is popping up with the needed data and the save as dialogue window also opens but when I select the name and save the file it is no where to be found the the new workbook stays open as (Book1 or whatever auto populates).
代码正在运行，新工作簿弹出所需的数据，另存为对话窗口也会打开，但是当我选择名称并保存文件时，找不到新工作簿保持打开状态（Book1 或无论自动填充）。
I would like for a Save As name to auto populate when the dialogue window opens and have it referencing to two cells (i.e. $A$11, $B$11) in the original workbook.
我希望在对话窗口打开时自动填充另存为名称，并让它引用原始工作簿中的两个单元格（即 $A$11、$B$11）。
Here is my VBA:
这是我的 VBA：
Option Explicit
Sub SaveConsolidatedDemandData()
' Macro2 Macro
'Dim NameFile As Variant
'With Worksheets("SO1")
'NameFile = .Range("M3") & "_" & .Range("C11") & "_" & .Range("B22") & ".xls"
'End With
Application.ScreenUpdating = False
'
'
Sheets("Consolidation").Select
Sheets("Consolidation").Copy
Application.GetSaveAsFilename
Application.ScreenUpdating = True
End Sub
回答by TJYen
Here is a macro showing/doing what you are asking for. Change the workbook name as well as sheet name as needed:
这是一个显示/执行您要求的宏。根据需要更改工作簿名称和工作表名称：
Sub test()
Dim wb, wbnew As Workbook
Dim ws As Worksheet
Dim filename As String
Set wb = Workbooks(2) 'Name of the workbook you are copying from
Set ws = wb.Sheets("Sheet1") 'Name of sheet you are copying
filename = ws.Range("A11") & "_" & ws.Range("B11")& ".xlsm"
'adds new workbook
Set wbnew = Workbooks.Add
wb.Activate
'copies sheet to new workbook
wb.Sheets("Sheet1").Copy Before:=wbnew.Sheets(1)
'Save as dialog box to save as excel file
Application.Dialogs(xlDialogSaveAs).Show filename
wbnew.Close
End Sub
