时间戳

首页 > 解决方案 > Copy active cell to last free cell in column A of another sheet

excel - Copy active cell to last free cell in column A of another sheet

问题描述

I want to copy the active cell to the last free cell in column A of another sheet called "comments".

I tried this code:

Sub Macro3()

    'Keyboard Shortcut: Ctrl y
    Dim LastRowMaster As Long

    'Take the cell I'm in
    ActiveCell.Select

    'Copy it
    Selection.Copy

    'Go to the sheet I want to paste into
    Sheets("comments").Select

    'Find the last row in my sheet "comments" and +1
    LastRowMaster = Worksheets("comments").Cells(Worksheets("comments").Rows.Count, 1).End(xlUp).Row + 1

    'Choose the last free cell in column A
    Range("A:").Copy Destination:=ThisWorkbook.Worksheets("comments").Cells(LastRowMaster, "A")

    'Copy my paste
    ActiveSheet.Paste

End Sub

I get an error

"Method 'range' of object_global failed

in this line:

Range("A:").Copy Destination:=ThisWorkbook.Worksheets("comments").Cells(LastRowMaster, "A")

标签： excelvba

解决方案

首先，阅读本文，您可以加快和缩短代码。

我认为这可以满足您的要求（A:不是有效的范围参考），但我不建议您将代码基于activecell.

Sub Macro3()

Dim LastRowMaster As Long

With Worksheets("comments")                                     'avoid repeating this on every line
    LastRowMaster = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
    ActiveCell.Copy Destination:=.Cells(LastRowMaster, "A")     'copy and paste on the same line
End With

End Sub

推荐阅读