使用VBA代码复制和粘贴数据 [英] Copying and pasting data using VBA code

问题描述

我在电子表格上有一个按钮，当按下该按钮时，应允许用户打开一个文件，然后复制电子表格数据的列A-G，然后将这些列中的数据粘贴到当前工作表上。

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.

我的代码中有一个逻辑错误;它运行，但它将选择粘贴在错误的地方。

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.

这是我的代码：

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

推荐答案

使用PasteSpecial方法：

Use the PasteSpecial method:

sht.Columns("A:G").Copy
Range("A1").PasteSpecial Paste:=xlPasteValues

但是你的大问题是， re c将您的ActiveSheet挂载到数据，而不是更改它。您不需要按照我的代码进行激活和选择（这假设您的按钮位于要复制的工作表上）。

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).

这篇关于使用VBA代码复制和粘贴数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！