使用 VBA 代码复制和粘贴数据

发布于 2024-11-25 05:50:50 字数 671 浏览 1 评论 0 原文

我在电子表格上有一个按钮，按下该按钮后，用户应该可以打开文件，然后复制电子表格“数据”的 AG 列，然后将这些列中的数据粘贴到当前工作表上。

我的代码有逻辑错误；它运行，但将选择粘贴到错误的位置。

我在引用这两本工作簿时遇到问题。

这是我的代码：

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

原文

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.

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦途 2024-12-02 05:50:50

使用 PasteSpecial 方法：

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

但是您的大问题是您将 ActiveSheet 更改为“数据”而不是将其更改回来。根据我的代码，您不需要执行激活和选择（这假设您的按钮位于要复制到的工作表上）。

Use the PasteSpecial method:

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

回复收藏 0 原文

下雨或天晴 2024-12-02 05:50:50

'所以从这次讨论中我认为这应该是代码。

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

“请告诉我这是否正确或遗漏了某个步骤。谢谢。

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

回复收藏 0 原文

~没有更多了~