Excel 2010 vba 复制选择工作表，保存并关闭两个工作簿
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19862140/
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 2010 vba copy select sheets, save and close both workbooks
提问by user2921708
I have a workbook that creates 3 logs. Before close I need those three sheets to copy into a new workbook then I need both workbooks to save and close themselves. I've gotten as far as the code below and now when I try to run the module it brings up the macro window and won't run. I do have this stored in ThisWorkbook. Any input would be much appreciated.
我有一个创建 3 个日志的工作簿。在关闭之前，我需要将这三张工作表复制到一个新的工作簿中，然后我需要两个工作簿来保存和关闭自己。我已经得到了下面的代码，现在当我尝试运行该模块时，它会显示宏窗口并且不会运行。我确实将其存储在 ThisWorkbook 中。任何输入将不胜感激。
Mary
Option Explicit
Private Sub BeforeClose(Cancel As Boolean)
'Save worksheets to new workbook
Dim strSaveName As String
Dim DstFile As String 'destination File name
Dim DataWorkbook As Workbook
'Unhide Worksheets
Set DataWorkbook = ActiveWorkbook
DataWorkbook.Sheets("Service Change Log").Visible = True
DataWorkbook.Sheets("Transaction Log").Visible = True
DataWorkbook.Sheets("Call Initiation Log").Visible = True
ChDir "P:\CSR Rollback Tool\Test"
strSaveName = Replace(Application.UserName, ",", "")
'Copy Sheets
Sheets(Array("Service Change Log", "Transaction Log", "Call Initiation Log")).Copy
'Hide Worksheets
Sheets("Service Change Log").Visible = False
Sheets("Transaction Log").Visible = False
Sheets("Call Initiation Log").Visible = False
'Close Worksheet
MsgBox "P:\CSR Rollback Tool\" & strSaveName & Format(Now(), "yyyymmdd hh-mm") & ".xlsx"
Application.DisplayAlerts = False
DstFile = "P:\CSR Rollback Tool\test\" & strSaveName & Format(Now(), "yyyymmdd hh-mm") & ".xlsx"
Workbooks(DstFile).Close SaveChanges:=True
DataWorkbook.Close Save:=True
End Sub
回答by jaybee3
The newly created workbook will have focus after:
新创建的工作簿将在以下情况下获得焦点：
'Copy Sheets
Sheets(Array("Service Change Log", "Transaction Log", "Call Initiation Log")).Copy
And so:
所以：
'Hide Worksheets
Sheets("Service Change Log").Visible = False
Sheets("Transaction Log").Visible = False
Sheets("Call Initiation Log").Visible = False
will try and hide the sheets, but you can't have a workbook with no visible sheets* so you just need to rejig your code slightly and it should work.
将尝试隐藏工作表，但您不能拥有没有可见工作表*的工作簿，因此您只需要稍微重新调整代码即可。
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Save worksheets to new workbook
Dim strSaveName As String
Dim DstFile As String 'destination File name
Dim DataWorkbook As Workbook
'Unhide Worksheets
Set DataWorkbook = ActiveWorkbook
DataWorkbook.Sheets("Service Change Log").Visible = True
DataWorkbook.Sheets("Transaction Log").Visible = True
DataWorkbook.Sheets("Call Initiation Log").Visible = True
ChDir "P:\CSR Rollback Tool\Test"
strSaveName = Replace(Application.UserName, ",", "")
'Copy Sheets
Sheets(Array("Service Change Log", "Transaction Log", "Call Initiation Log")).Copy
'Save new workbook
MsgBox "P:\CSR Rollback Tool\" & strSaveName & Format(Now(), "yyyymmdd hh-mm") & ".xlsx"
Application.DisplayAlerts = False
DstFile = "P:\CSR Rollback Tool\test\" & strSaveName & Format(Now(), "yyyymmdd hh-mm") & ".xlsx"
activeworkbook.saveas(DstFile)
activeworkbook.close true
'Hide Worksheets in original workbook
DataWorkbook.Sheets("Service Change Log").Visible = False
DataWorkbook.Sheets("Transaction Log").Visible = False
DataWorkbook.Sheets("Call Initiation Log").Visible = False
'Close Worksheet
DataWorkbook.Close Save:=True
End Sub
回答by jacouh
First try this, change event handler name as:
首先试试这个，将事件处理程序名称更改为：
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Instead of:
代替：
Private Sub BeforeClose(Cancel As Boolean)
