Excel VBA - 在没有警告的情况下移动或复制工作表的代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8040626/
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 VBA - Code to move or copy worksheets without warnings
提问by Zane
I am trying to create a macro that would act the same as right clicking a workbooktab, selecting move or copy, checking the copy option, selecting another open workbookand clicking ok but without the warnings. I found the code to disable warning and I was able to record a macro that does what I want but I don't know how to make it request which open workbookto copy to.
我正在尝试创建一个宏，其作用与右键单击工作簿选项卡、选择移动或复制、检查复制选项、选择另一个打开的工作簿并单击确定但没有警告相同。我找到了禁用警告的代码，并且我能够录制一个可以执行我想要的操作的宏，但我不知道如何让它请求复制到哪个打开的工作簿。
In short how do I make the following code work where WorksheetIWantToCopyis the one the user currently has selected and OpenWorkbookIWantToCopyToo.xlsxis a workbookto be selected by the user out of a list of open workbooks.
总之我怎么让下面的代码工作，其中WorksheetIWantToCopy是一个用户当前已选定并OpenWorkbookIWantToCopyToo.xlsx是一个工作簿由用户选择了开放名单的工作簿。
Application.DisplayAlerts = False
Sheets("**WorksheetIWantToCopy**").Select
Sheets("**WorksheetIWantToCopy**").Copy Before:=Workbooks( _
"**OpenWorkbookIWantToCopyToo.xlsx**").Sheets(1)
I appreciate any information anyone can provide. My team greatly appreciates your support (we currently have to hit ok on 25 warnings due to conflicts we don't really care about). Thx!
我感谢任何人可以提供的任何信息。我的团队非常感谢您的支持（由于我们并不真正关心的冲突，我们目前必须在 25 个警告上点击确定）。谢谢！
回答by mischab1
If the worksheet you want to copy will always be the active sheet then you can use ActiveSheet.
如果您要复制的工作表始终是活动工作表，那么您可以使用ActiveSheet.
As for letting the user select a workbook, it can be as simple as using the InputBox.
至于让用户选择工作簿，可以像使用InputBox一样简单。
Public Function getWorkbookName() As String
Dim i As Integer, sListOfWbks As String, sRsp As String
' build list of workbooks
For i = 1 To Workbooks.Count
sListOfWbks = sWbkList & vbCrLf & i & " - " & Workbooks(i).Name
Next i
sRsp = InputBox("Select workbook." & vbCrLf & sListOfWbks)
If IsNumeric(sRsp) Then
getWorkbookName = Workbooks(CInt(sRsp)).Name
Else
' user pressed cancel or entered invalid text
getWorkbookName = ""
End If
End Function
This basic example will of course list allworkbooks, including hidden add-ins and the workbook you are moving away from.
这个基本示例当然会列出所有工作簿，包括隐藏的加载项和您要离开的工作簿。
回答by Steve Taylor
This needs to be said before anything else: always, always, ALWAYSmake use of .Copyinstead of .Movewhen automatically shuffling excel workbooks with VBA. Movehas inherent risks because it is a modification of the other file, and if your code misbehaves then you could lose all of the data you're working with.
这需要在其他任何事情之前说：始终，始终，始终使用.Copy而不是.Move在使用 VBA 自动改组 excel 工作簿时使用。Move具有固有风险，因为它是对另一个文件的修改，如果您的代码行为不端，那么您可能会丢失正在使用的所有数据。
First of all, know which workbook is which, with no ambiguity:
首先，知道哪个工作簿是哪个，没有歧义：
Dim wkbkDestination, wkbkTemporary As Workbook
Set wkbkDestination = Workbooks("OpenWorkbookIWantToCopyTo.xlsx")
Set wkbkTemporary = Workbooks.Open("WorkbookIWantToCopy.xlsx")
Next, Copy your desired tab to your destination workbook, rename the new tab to prevent errors, and close the second workbook, without saving.
接下来，将所需的选项卡复制到目标工作簿，重命名新选项卡以防止出现错误，然后关闭第二个工作簿，而不保存。
wkbkTemporary.Worksheets("WorksheetIWantToCopy").Copy Before:=wkbkDestination.Worksheets(1)
wkbkDestination.Worksheets(1).Name = "WorkbookIWantToCopy"
wkbkTemporary.Close SaveChanges = False
Naturally, depending on the exact controls you intend to use, there are lots of ways this code could be implemented. From your description it is somewhat unclear what exact problem you're trying to solve and whether this is a one-off event you're trying to accomplish for a given list of files, or whether this function is to be used on an ongoing basis.
当然，根据您打算使用的确切控件，可以通过多种方式实现此代码。根据您的描述，有些不清楚您要解决的具体问题是什么，这是否是您要针对给定文件列表完成的一次性事件，还是要持续使用此功能.
