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

Question

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

score 1 · Answer 1 · answered Oct 10 '19 at 09:10

1

First, read this and you can speed up and shorten your code.

I think this does what you want (A: is not a valid range reference), but I would not advise you to base code on the 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

answered Oct 10 '19 at 09:10

SJR

22,986
6
18
26

Hi SJR, Thanks for the response. It seems to be that kind of code I’m looking for. Thanks! But I forgot that the values I try to copy is from a formula. So, I get an error. Can I do something, so I only copy the values? And not the formula? Something like: .PasteSpecial (xlPasteValuesAndNumberFormats) Is that possible? Thanks again for taking time to help me. – Martin Oct 10 '19 at 19:30
You can use `.Cells(LastRowMaster, "A").value=activecell.value` to just copy the value, or the approach you suggest (the pastespecial bit needs to be on a separate line). – SJR Oct 10 '19 at 19:35

score 0 · Answer 2 · answered Oct 10 '19 at 19:51

0

So the code will be like this?

Sub Macro3()

Dim LastRowMaster As Long 
    With Worksheets("comments")
    LastRowMaster = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
    ActiveCell.Copy Destination:=.Cells(LastRowMaster, "A").Value = ActiveCell.Value  
End With

End Sub

It comes up with an error. I properly just missed up a dot/ blank space or something. Aaargh, I can’t see the problem.

Copy method of Range class failed

answered Oct 10 '19 at 19:51

Martin

11
2

Try `.Cells(LastRowMaster, "A").Value = ActiveCell.Value` without `ActiveCell.Copy Destination:=` – dreojs16 Dec 20 '19 at 08:05

score 0 · Answer 3 · answered Dec 20 '19 at 08:09

This should copy your activecell onto the "comments"sheet in the next empty cell in column A:

Sub Macro3()

Dim LastRowMaster As Long 

    With Worksheets("comments")
       LastRowMaster = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
       .Cells(LastRowMaster, "A").Value = ActiveCell.Value  
    End With

End Sub

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

3 Answers3