April 2, 2014

Copy Row of Data from sheet1 to sheet2 with User-form

Posted on by Dinesh Kumar Takyar

Aamir, a website visitor, is trying to copy a row of data from sheet1 to sheet2 with a user form. The user-form has a text-box and a command button. On clicking on the command button after entering a number in the text box, the relevant row of data should be copied from Sheet1 to Sheet2 in an Excel workbook.
The complete VBA code or macro for the command button is given below:
Private Sub CommandButton1_Click()
Dim myrow As Integer
myrow = Val(TextBox1.Text)
Rows(myrow).Copy
Sheet2.Activate
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets(“sheet2”).Rows(erow)
Worksheets(“sheet1”).Activate

End Sub

Watch the training video:


View the video on YouTube

11 thoughts on “Copy Row of Data from sheet1 to sheet2 with User-form”

  2. Hi Dinesh sir I tried with the above mentioned code but getting below error.
    ‘subscript out of range’.
    Why so?please help me on it.

  6. I am trying to do a little bit different with the above code, instead of copying I want to cut and paste the entire row. So I change code to

    Rows(myrow).Cut

    else I use the same but I got a runtime error 9 script out of range. Can you please guide me.

    Thanks

    1. To cut a row of data from sheet1 to sheet2 you can use:
      Rows(4).Cut Destination:=Worksheets(“Sheet2”).Rows(4)

  7. Dear Mr. Dinesh kumar Takyar,

    My English is not so good…………can you e-mail me your Skype or whats app then i can explain you my problem in better way.

    Thanks in advance for kind cooperation.

  8. Hi there, i just wanna ask if you can do a Cut and Insert code with UserForm, here’s the scenario, I got a Search command and a Transfer Command, what I want to happen is every time I will search for a particular record using the Search Command, I will have a unique data which will be the Serial Number, then when I click Transfer Command, the entire row where the Serial Number located will going to get copied or cut from its row and inserted to the next blank row on the next sheet. Never mind the code of the Search Command, I just want the Transfer command code please, thanks a lot in advance !

  9. Mine is not working. When I run it copies fine but it pastes to row 9 every time on sheet 2

    Every time I run the form it selects the correct line and copies it from worksheet 1 to work sheet 2 but it does not list it just overwrites
    and keeps repasting in row 9 ?

    Here is my code thank you

    Private Sub CommandButton1_Click()

    Dim myrow As Integer
    myrow = Val(TextBox1.Text)

    Rows(myrow).Copy

    Sheet4.Activate
    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ActiveSheet.Paste Destination:=Worksheets(“worksheet2”).Rows(erow)
    Worksheets(“ONE”).Activate

    End Sub

Comments are closed.