Create button to copy row from Sheet 1 to Sheet 2

Question

I have Worksheet 1, with columns A to D.

I would like to create a button executing row to be copied to Worksheet 2, as soon as cell C in Worksheet 1 is populated.

I have no experience in Excel at all, so far I found and altered this macro code for my needs:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 3 And Target.Cells.Count = 1 Then
        Target.EntireRow.Copy _
        Destination:=Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    End If
End Sub

But when I try to create a button to execute this macro, it would never work. Could anyone help me solve this, please.

This already does what you want, If you want to execute this macro on button press then move this code to `Button Click event` http://office.microsoft.com/en-us/excel-help/add-a-button-and-assign-a-macro-to-it-in-a-worksheet-HP010236676.aspx — Ravi Yenugu, Sep 04 '14 at 13:27

score 0 · Answer 1 · edited May 23 '17 at 10:33

0

Is this what you are trying? Read more about Worksheet_Change HERE

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim lRow As Long

    On Error GoTo Whoa

    Application.EnableEvents = False

    If Target.Cells.CountLarge > 1 Then Exit Sub

    Set ws = ThisWorkbook.Sheets("Sheet2")
    lRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row + 1

    If Not Intersect(Target, Columns(3)) Is Nothing Then _
    Target.EntireRow.Copy Destination:=ws.Rows(lRow)

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

EDIT:

If the code still doesn't work then from the VBA Editor, press CTRL + G to bring up the immediate window and type this

Application.EnableEvents = True

and press ENTER key and now the code should work.

enter image description here

edited May 23 '17 at 10:33

Community

1
1

answered Sep 04 '14 at 13:23

Siddharth Rout

147,039
17
206
250

Siddharth, thank you for your answer, but the macro you wrote doesn't seem to work for me. The code I've added in my question: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 And Target.Cells.Count = 1 Then Target.EntireRow.Copy _ Destination:=Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) End If End Sub works for me but I don't know how to assign this macro to a button so the macro would only be executed when the button is pressed. Any idea? Thanks, Jost – Jošt Murovec Sep 05 '14 at 07:45
The above code is tried and tested. So "doesn't seem to work for me" doesn't give me enough information to help you :) – Siddharth Rout Sep 05 '14 at 07:47
Also, the problem with my code is, the entire row is copied into Sheet 2 even if "delete" key is pressed in the column C - what I want is the row to only be copied when quantity is added (let's say 10). Also, I would like the row to be deleted from sheet 2 if the quantity is deleted from row C in sheet 1. Thanks, Jost – Jošt Murovec Sep 05 '14 at 07:55
Siddharth - you are right. Actually nothing happens in Sheet 2 using this macro. But I do get "Subscript out of range" message when I put in number in column C. – Jošt Murovec Sep 05 '14 at 07:58
Did you change `Set ws = ThisWorkbook.Sheets("Sheet2")` to reflect the correct sheet? else you will get the error that you mentioned above – Siddharth Rout Sep 05 '14 at 08:01
I just renamed my Sheets back to Sheet1 and Sheet2 and the error is gone. Still nothing happens in Sheet2. – Jošt Murovec Sep 05 '14 at 08:06
Siddharth, thank you for your help. Just wanted you to know I solved my problem. Have a good day. – Jošt Murovec Sep 06 '14 at 08:05

Create button to copy row from Sheet 1 to Sheet 2

1 Answers1