The copied Data pasted on basis of worksheet cell value and command button determine which row to paste.

  • Hello There,

    Hope all are fine. There is something that turning my head down. Please.

    I have work sheet which have all the data.

    I need to copy data from one sheet to another. I am using a command button to copy specific row to another sheet. The code is below.


    Code
    1. Sub Button1_Click()
    2. Dim i As Integer, LastRow As Long
    3. i = InputBox("Please enter the row number to copy", "Select Row", "8 - 1250")
    4. Sheets("All Data").Range(Cells(i, 3), Cells(i, 6)).Copy
    5. Sheets("MS").Select
    6. LastRow = Sheets("MS").Cells(Rows.Count, "C").End(xlUp).Row
    7. Cells(LastRow + 1, 2).Select
    8. ActiveSheet.Paste
    9. Application.CutCopyMode = False
    10. End Sub

    but every time I need to change the destination sheet in the code to paste data to various sheet.

    It would be easy that I typed the sheet name in the source sheet "All Data" in G2 cell in which the copied data will be pasted.

    When I click on the command button it copied data as above code and pasted data to the sheet specified on cell "G2" of the source sheet.


    Hope some one understand my thoughts. If its possible then what should I change in the above code.


    Thanks in advance.

  • Try:

    Code
    1. Sub Button1_Click()
    2. Dim i As Integer, LastRow As Long
    3. i = InputBox("Please enter the row number to copy", "Select Row", "8 - 1250")
    4. With Sheets("All Data")
    5. .Range(.Cells(i, 3), .Cells(i, 6)).Copy Sheets(.Range("G2").Value).Cells(Sheets(.Range("G2").Value).Rows.Count, "B").End(xlUp).Offset(1)
    6. End With
    7. End Sub

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hello, Mumps one thing I need say that,

    After the row data paste complete the sheet moves to the destination sheet to check if the exact data pasted there or not.

    In previous code that happened but in this code its not happening.

    What to do? Please.

  • Try

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • You're welcome, and thanks for the Like

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi, KjBox,

    Hope you are fine.

    I want to modify the above code for some purpose,

    In the above code, the data copy from 3 to 6 but If I have copy data 3 to 6 and 8

    then what will be the code. I tried to modify but it gives me an error.


    Will you tell me what will be the code for the above condition.


    Thanks

  • Do you mean you want the cell in column H copied to G2 (the cell immediately after the copied block of data), or to H2?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Thank you Kjbox for Your kind response,

    Let me tell you by the code below.

    Code
    1. Sub Button1_Click()
    2. Dim i As Integer, LastRow As Long
    3. i = InputBox("Please enter the row number to copy", "Select Row", "8 - 1250")
    4. With Sheets("All Data")
    5. .Range(.Cells(i, 3), .Cells(i, 6)).Copy Sheets(.Range("G2").Value).Cells(Sheets(.Range("G2").Value).Rows.Count, "B").End(xlUp).Offset(1)
    6. End With
    7. End Sub

    In the above code, the blue color tells me what range should copy. It copies from cell 3 to 6.

    but I want to copy cell 3 to 6 as well as cell 8 and paste it to the sheet which G2 defines.

    The code is fine, I just want to add another cell in the range.


    Thanks

  • Try

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.