Create command button to copy range and paste in another worksheet based on the source file cell value

mamun_ges

Board Regular
Joined
Jul 21, 2016
Messages
52
I know this is odd to comment on a solved problem Create command button to copy range and paste in another wrk sheet but I have a similar problem. So I can't help myself.
I have been using the below code
VBA Code:
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 now I have to change the destination worksheet every time to paste the copied data.
If it is possible that the source worksheet cell A1 has the destination worksheet name( "MS" or any other worksheet). when I click on the button it copied data and paste it to the destination sheet based on the cell A1 of the source file named "All Data".

Hope someone helps me to resolve my problem.

Thanks in advance.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,155
Try this :

VBA Code:
Option Explicit

Sub Button1_Click()
Dim i As Integer, LastRow As Long
Dim x As String
x = Range("A1").Value

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(x).Select
LastRow = Sheets(x).Cells(Rows.Count, "C").End(xlUp).Row
Cells(LastRow + 1, 2).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
 
Solution

mamun_ges

Board Regular
Joined
Jul 21, 2016
Messages
52
You are awesome, Thanks. Wish you a good life.
:thumbup:
:thumbup:
:thumbup:
 

Forum statistics

Threads
1,141,619
Messages
5,707,445
Members
421,508
Latest member
someinternetuser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top