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

Jd1uth

New Member
Joined
Sep 17, 2024
Messages
12
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA Code:
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
 
Last edited by a moderator:
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.
1726697838069.png


Put the following code in userform:
VBA Code:
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

😇
 
Upvote 0
Solution

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hallo DanteAmore, and we succeed today works 100%
Thank you for the help.(y)
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.
 
Upvote 0

Forum statistics

Threads
1,224,595
Messages
6,179,798
Members
452,943
Latest member
Newbie4296

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