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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
 
Upvote 0
Solution
You are awesome, Thanks. Wish you a good life.
:thumbup:
:thumbup:
:thumbup:
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

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
Back
Top