Excel VBA：通过命令按钮为所有工作表复制和粘贴数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16691890/
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: Copying and Pasting Data via CommandButton for all worksheets
提问by jcv
I'm new to VBA and need some help with implementing a tool:
我是 VBA 新手，需要一些帮助来实现工具：
Essentially, I want to create a CommandButton that automatically copies range D9:L18 and pastes it to Q9:Y18 for my active worksheet. Furthermore, once I press the button, I want this process to be repeated for all other worksheets (around 40) with the exception of two specific ones.
本质上，我想创建一个 CommandButton，它会自动复制范围 D9:L18 并将其粘贴到 Q9:Y18 以用于我的活动工作表。此外，一旦我按下按钮，我希望对所有其他工作表（大约 40 个）重复此过程，但两个特定的工作表除外。
Can you please give me a hand with this?
你能帮我处理一下吗？
Your help is much appreciated - many thanks indeed.
非常感谢您的帮助 - 非常感谢。
Edit:
编辑：
There is an error @ Sheet.Range("D12:L18").Selectfor some reason - perhaps you can help me? Thanks.
@Sheet.Range("D12:L18").Select出于某种原因出现错误- 也许您可以帮助我？谢谢。
Private Sub CommandButton1_Click()
Dim Sheet As Worksheet
For Each Sheet In ThisWorkbook.Worksheets
If Sheet.Name <> "Definitions" And Sheet.Name <> "fx" And Sheet.Name <> "Needs" Then
Sheet.Range("D12:L18").Select
Selection.Copy
Sheet.Range("Q12:Y18").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
Next
End Sub
回答by Daniel M?ller
Recording a macro to copy and paste the cells you want is the best way to get the code to copy a single sheet.
录制宏以复制和粘贴所需的单元格是获取代码以复制单个工作表的最佳方式。
But to do the work in all your sheets, use this:
但是要在所有工作表中完成工作，请使用以下命令：
Private Sub CommandButton1_Click()
Dim Sheet As Worksheet
For Each Sheet In ThisWorkbook.Worksheets
If Sheet.Name <> "Definitions" And Sheet.Name <> "fx" And Sheet.Name <> "Needs" Then
Sheet.Range("D12:L18").Copy
Sheet.Range("Q12:Y18").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
Next
End Sub
To add the button, go to the developertab in excel, add an ActiveXbutton.
Design mode will be turned on. Double click that button, the code to be run when it's clicked will be shown in VBA editor. Put your copy code there inside the Private Sub YourButtonName_Click()method.
要添加按钮，请转到developerexcel中的选项卡，添加一个ActiveX按钮。设计模式将被打开。双击该按钮，单击它时要运行的代码将显示在 VBA 编辑器中。将您的复制代码放在Private Sub YourButtonName_Click()方法中。
To make the button work, turn off the design mode. (That's done in the developer tab in excel window)
要使按钮工作，请关闭设计模式。（这是在excel窗口的开发人员选项卡中完成的）
