Copy cells selected by user and paste to location that they select

[mamun_ges]

Hi,

I have found help from the Thread

Copy cells selected by user and paste to location that they select

the Code is

Sub YesMacro()
    On Error Resume Next
    Set Ret = Application.InputBox(Prompt:="Please select a range where you want to paste", Type:=8)
    On Error GoTo 0
    If Not Ret Is Nothing Then
        Selection.Copy
        Ret.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    End If
End Sub

but the problem of mine is I want to copy data to another sheet in same workbook. Then what will be the code is?

Thanks in Advance

 

[vcoolio]

Hello Mamun-ges,

Perhaps the following may help:-

Sub Test()

   Dim rng As Range
   
On Error Resume Next
Set rng = Application.InputBox(Prompt:="Select range to be moved.", Title:="Select Ranges", Type:=8)

Application.ScreenUpdating = False

   If Not rng Is Nothing Then
   rng.Copy
   Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
   End If
   
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[mamun_ges]

Thank you for your support vcoolio

Actually I want to paste data in sheet two which I will select and this is changeable, It may be B column or C column or other. So it will be helpful for me if I create a active x Control to update, then a popup

like that where I will Input in which column data will be paste, the data will always paste in sheet 2 but in different columns.

Can it done?

Thank You.

 

[vcoolio]

Hello Mamun_ges,

.....the data will always paste in sheet 2 but in different columns.

Your image link doesn't work, however try the amended code below, assigned to a button:-

Sub Test()

    Dim rng As Range
    Dim cSearch As String

Application.ScreenUpdating = False

Set rng = Application.InputBox(Prompt:="Select range to be moved.", Title:="Select Ranges", Type:=8)
cSearch = InputBox("Please enter a column letter.")
If cSearch = vbNullString Then Exit Sub

   If Not rng Is Nothing Then
   rng.Copy Sheet2.Range(cSearch & Rows.Count).End(3)(2)
   End If
   
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

With the code, an input box will appear asking you to select a range to copy then another input box will appear asking you to input the column letter to which you wish to transfer the range (in sheet2).

I hope that this helps.

Cheerio,
vcoolio.