Excel: Creating a macro to copy&paste active selection into another worksheet

Question

This is my first question on here, so please be kind.

Disclaimer: I'm also very new to macro building in Excel, so don't be upset if I ask for clarification.

I set out to make a project tracker and ran into the following problem: I would like to have a button that would allow me to copy an active selection (a single cell or a row) on one worksheet and paste it into another worksheet within one workbook.

What I'm essentially trying to do is to have a possibility to just select a certain task, click the button and copy/paste it into a different worksheet. Therefore, as far as I understand, the code needs to be a loop (?) and it also needs to offset so that I can paste multiple tasks and they don't overwrite each other.

The code I managed to scrounge up right now gives me the runtime error 1004 on the line Activecell.Offset, and I can't figure out why.

Sub MoveToWeekly()
Sheets("Test1").Select
    Selection.Copy

    Sheets("Test2").Select

    Sheets("Test2").Range("A1:A4").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1).Select
    
    

    Selection.PasteSpecial _
        Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End Sub

I'd be incredibly glad for some help and I'm sorry that I'm not being awfully specific.

Most likely `ActiveCell` is the last cell in the column at that point. You can verify by using `Debug.Print ActiveCell.Address`. — BigBen, Jun 23 '20 at 17:35
Thank you for the quick response! It is indeed the last cell in the column, I just don't know why it jumps there. — Karina K., Jun 23 '20 at 17:41
Normally you use `End(xlUp)` to find the last row, see [this](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). — BigBen, Jun 23 '20 at 17:44

score 1 · Accepted Answer · answered Jun 23 '20 at 17:47

Here's one approach

Sub MoveToWeekly()

    'make sure a range is selected
    If typeName(selection)<>"Range" Then
         Msgbox "First select a range"
         Exit sub
    End If

    'you can assign the value between the two ranges directly, without copy/paste
    'Note: assumes there's always a value in ColA on the destination sheet
    with Selection
        'Starting at the bottom of the sheet and using xlUp is 
        '    typically safer than using xlDown
        Sheets("Test2").Cells(rows.count,1).end(xlup).offset(1,0) _ 
              .resize(.rows.count, .columns.count).value = .value
    End with

End Sub

Thank you tons, Tim! Works like a charm. – Karina K. Jun 24 '20 at 14:25 — Karina K., Jun 24 '20 at 14:25

Excel: Creating a macro to copy&paste active selection into another worksheet

1 Answers1