Excel VBA-将范围从一张纸复制到另一张纸，下一个空行 [英] Excel VBA - Copy range from one sheet to another, next empty row

问题描述

我正在尝试从一个工作表获取一个范围，并将其复制到另一工作表中的下一个空行(基本上，将范围粘贴到另一工作表中的下一个空行的范围 A12:D12 ).范围永远不会改变.我已经看到很多类似的问题，有人说答案很好，但我无法解决.

I'm trying to take a range from one sheet and copy it to the next empty row in another sheet (basically, paste into the range A12:D12 for next empty row in the other sheet). The range will never change. I've seen a lot of questions like this, with people saying the answers work great, but I can't get it to work.

VBA的新手.这是我正在使用的代码:

Very new to VBA. Here is the code I'm using:

Private Sub CommandButton1_Click()

    Dim NextRow As Range
    Set NextRow = Range("A" & Sheets("Sheet3").UsedRange.Rows.Count + 1)  

    Sheet1.Range("A12:D12").Copy
    Sheet3.Activate
    NextRow.PasteSpecial Paste:=xlValues, Transpose:=False
    Application.CutCopyMode = False

    Set NextRow = Nothing

End Sub

这会运行，但实际上不会将任何值粘贴到 Sheet3 中.有什么我想念的吗?有更好的方法吗?

This runs but it doesn't actually paste any values into Sheet3. Is there something I'm missing? Is there a better way to do this?

谢谢！

推荐答案

您在定义 NextRow 的第二行中遇到了问题.
有一个更好的方法吗?这取决于您的需求；我个人不喜欢在VBA宏中激活/选择其他单元格，例如我会摆脱 Sheet3.Activate .我也将手动"复制内容而不使用剪贴板，以避免更改用户的剪贴板内容.

You just had an issue in the second line defining NextRow.
Is there a better way to do this? It depends on your needs; personally I do not like to activate/select other cells during a VBA macro so e.g. I would get rid of the Sheet3.Activate. I would also copy the stuff 'manually' without using the clipboard to avoid changing the user's clipboard contents.

Private Sub CommandButton1_Click()

    Dim NextRow As Range
    Set NextRow = Sheet3.Range("A" & Sheet3.Rows.Count).End(xlUp).Offset(1, 0)

    Sheet1.Range("A12:D12").Copy
    Sheet3.Activate
    NextRow.PasteSpecial Paste:=xlValues, Transpose:=False
    Application.CutCopyMode = False

    Set NextRow = Nothing

End Sub

这篇关于Excel VBA-将范围从一张纸复制到另一张纸，下一个空行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！