Copy /past cells by inputbox on sheet 2 to sheet1 using VBA

[Jd1uth]

Hallo i am new and hope you can help.
I am trying to copy a range cells by search on sheet 2 with a inputbox then select the cells and copy them on sheet 1 by inputbox select cell/cells to copy to.
This works ok but i want to stay on sheet 1, now it going to sheet 2 and i have to go back to sheet 1 manually.
I use this code below can someone help.

Sub Find_First()
Dim FindString As String
Dim rng As Range
FindString = InputBox("Enter a Search value")
If Trim(FindString) <> "" Then
    With Sheets("Sheet2").Range("A:M") 'searches all of column A to M
        Set rng = .Find(What:=FindString, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not rng Is Nothing Then
            Application.Goto rng, False 'value found
        Else
            MsgBox "Nothing found" 'value not found
        End If
    End With
End If
ActiveCell.CurrentRegion.Select
   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:=xlPasteAll, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    End If
  
End Sub

 

[DanteAmor]

I will try to make a Userform next and get the code in there. I Hope

Try the following. Create the textbox, refedit controls and a command button in the userform.

Put the following code in userform:

Private Sub CommandButton1_Click()
  Dim FindString As String
  Dim rng As Range, ret As Range
  Dim nTime As Variant
  Dim i As Long
  
  If TextBox1.Value = "" Then Exit Sub
  If RefEdit1.Value = "" Then Exit Sub
  If TextBox2.Value = "" Then Exit Sub
  If Not IsNumeric(TextBox2.Value) Then Exit Sub
  
  FindString = TextBox1.Value
  With Sheets("Sheet2").Range("A3:DM3") 'searches all of column A to DM in row 3
    Set rng = .Find(FindString, , xlValues, xlWhole, xlByRows, xlNext, False)
  End With
  
  If Not rng Is Nothing Then
    Set ret = Range(RefEdit1.Value)
    nTime = Val(TextBox2.Value)
    rng.CurrentRegion.Copy
    For i = 1 To nTime
      ret.PasteSpecial xlPasteValues
      ret.PasteSpecial xlPasteFormats
      Set ret = Cells(Rows.Count, ret.Column).End(3)(2)
    Next
  Else
    MsgBox "Nothing found" 'value not found
  End If
  Unload Me
End Sub

 

[Jd1uth]

Hallo DanteAmore, and we succeed today works 100%
Thank you for the help.
Now I will have a try to create un undo function, I found that it is not possible when using a vba script.
but found Excel: Undo With Excel VBA: Class Modules (1) do you now that this is working with the code we have.

 

[DanteAmor]

Now I will have a try to create un undo function

Create a new thread for the Undo topic, so you have more help.