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.

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

Thank You Mumps for responding.

it says run Time error '9':

subscript out of range.

Mumps, Thank you so much.

The code works fine..

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.

You are awesome, Thanks. Wish you a good life.

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

Thank you Kjbox for Your kind response,

Let me tell you by the code below.

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

to the adjacent cell of 3 to 6 where pasted.

I am speechless. How it works!!!

Thank you for all the great things you did. Wish you a happy life. .