使用 VBA 代码复制和粘贴数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6778759/
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
Copying and pasting data using VBA code
提问by spookymodem
I have a button on a spreadsheet that, when pressed, should allow the user to open a file, then copy columns A-G of the spreadsheet "Data", then paste the data from those columns on the current sheet.
我在电子表格上有一个按钮，按下该按钮时，应该允许用户打开一个文件，然后复制电子表格“数据”的 AG 列，然后将这些列中的数据粘贴到当前工作表上。
I have a logic error in the code; it runs, but it pastes the selection in the wrong place.
我在代码中有一个逻辑错误；它运行，但它将选择粘贴到错误的位置。
I am having trouble referencing the two workbooks.
我在引用这两本工作簿时遇到问题。
Here is my code:
这是我的代码：
Sub Button1_Click()
Dim excel As excel.Application
Dim wb As excel.Workbook
Dim sht As excel.Worksheet
Dim f As Object
Set f = Application.FileDialog(3)
f.AllowMultiSelect = False
f.Show
Set excel = CreateObject("excel.Application")
Set wb = excel.Workbooks.Open(f.SelectedItems(1))
Set sht = wb.Worksheets("Data")
sht.Activate
sht.Columns("A:G").Select
Selection.Copy
Range("A1").Select
ActiveSheet.Paste
wb.Close
End Sub
回答by Lance Roberts
Use the PasteSpecial method:
使用 PasteSpecial 方法：
sht.Columns("A:G").Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
BUT your big problem is that you're changing your ActiveSheet to "Data" and not changing it back. You don't need to do the Activate and Select, as per my code (this assumes your button is on the sheet you want to copy to).
但是您的大问题是您将 ActiveSheet 更改为“数据”而不是将其更改回来。根据我的代码，您不需要执行 Activate 和 Select 操作（假设您的按钮位于您要复制到的工作表上）。
回答by D1g1t4Lnrg
'So from this discussion i am thinking this should be the code then.
'所以从这次讨论中，我认为这应该是代码。
Sub Button1_Click()
Dim excel As excel.Application
Dim wb As excel.Workbook
Dim sht As excel.Worksheet
Dim f As Object
Set f = Application.FileDialog(3)
f.AllowMultiSelect = False
f.Show
Set excel = CreateObject("excel.Application")
Set wb = excel.Workbooks.Open(f.SelectedItems(1))
Set sht = wb.Worksheets("Data")
sht.Activate
sht.Columns("A:G").Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
wb.Close
End Sub
'Let me know if this is correct or a step was missed. Thx.
'让我知道这是正确的还是遗漏了一步。谢谢。
