Macro or VBA code

nerdbird

New Member
Joined
Oct 5, 2015
Messages
8
I am in need of a macro or VBA code that when ran will pop up a box and allow the user of the sheet to enter a name for example and the macro would search for the name entered and when it finds that name it will copy the entire row and paste it to a specific sheet. However I don't know if its possible to have the pop up ask for a the name to search for and the sheet to paste it too.

I have been using more targeted codes like:

Code:
Sub Name()

    Dim LSearchRow As Integer
    Dim LCopyToRow As Integer
    
    On Error GoTo Err_Execute
    
    'Start search in row 4
    LSearchRow = 4
    
    'Start copying data to row 2 in Sheet2 (row counter variable)
    LCopyToRow = 2
    
    While Len(Range("A" & CStr(LSearchRow)).Value) > 0
        
        'If value in column B = "Mail Box", copy entire row to Sheet2
        If Range("B" & CStr(LSearchRow)).Value = "Jorge Ortega" Then
            
            'Select row in Sheet1 to copy
            Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
            Selection.Copy
            
            'Paste row into Sheet2 in next row
            Sheets(“Name").Select
            Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
            ActiveSheet.Paste
            
            'Move counter to next row
            LCopyToRow = LCopyToRow + 1
            
            'Go back to Sheet1 to continue searching
            Sheets(“Sheet").Select
            
        End If
        
        LSearchRow = LSearchRow + 1
        
    Wend
    
    'Position on cell A3
    Application.CutCopyMode = False
    Range("A3").Select
    
    Exit Sub
    
Err_Execute:
    MsgBox "An error occurred."
    
End Sub


Not sure if there is a way to alter it to achieve what I am looking to do.
 
Last edited by a moderator:
What column will we be searching for these inputted words. Will we searching just one sheet?
 
Upvote 0
Yes it will just search one sheet of data. and it will be searching Column B for the name designated by the user. I know I can enter Joe into the above code and then sheet 2 and it will search for joe and paste his data to sheet 2. however what i want to achieve is when you run the code it pops up saying Enter Name: (they would enter the name the code would search for here) and then Enter Sheet: They would enter the sheet name here. Trying to Automate a process for some of my team.
 
Upvote 0
Try this:
Since your happy with your code and it works for you I just added a way for you to choose the word to search for and the sheet to paste to. Let me know if this works for you.
Code:
Sub Name()

    Dim LSearchRow As Integer
    Dim LCopyToRow As Integer
    Dim ans As String
    Dim ans2 As String
    ans = InputBox("And We Will Be Searching For What Word?")
    ans2 = InputBox("What Sheet Will We Be Pasting This Into?")
    
    On Error GoTo Err_Execute
    
    'Start search in row 4
    LSearchRow = 4
    
    'Start copying data to row 2 in Sheet2 (row counter variable)
    LCopyToRow = 2
    
    While Len(Range("A" & CStr(LSearchRow)).Value) > 0
        
        'If value in column B = "Mail Box", copy entire row to Sheet2
        If Range("B" & CStr(LSearchRow)).Value = ans Then
            
            'Select row in Sheet1 to copy
            Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
            Selection.Copy
            
            'Paste row into Sheet2 in next row
            Sheets(ans2).Select
            Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
            ActiveSheet.Paste
            
            'Move counter to next row
            LCopyToRow = LCopyToRow + 1
            
            'Go back to Sheet1 to continue searching
            Sheets("Sheet").Select
            
        End If
        
        LSearchRow = LSearchRow + 1
        
    Wend
    
    'Position on cell A3
    Application.CutCopyMode = False
    Range("A3").Select
    
    Exit Sub
    
Err_Execute:
    MsgBox "An error occurred."
    
End Sub
 
Upvote 0

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