Copy function in VBA

Question

Thank you so much for the support. I saw where the mistake was.

I wonder if its possible to make the input box as a drop down as well?

---

This code seems not to copy to "Sheet2"

I have this data set

But when I checked my "Sheet2" it is blank. Did I miss something?

Thanks a lot for your advice

`Sub SearchForString()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim LSearchValue As String

On Error GoTo Err_Execute

LSearchValue = InputBox("Please enter a value to search for.", "Enter value")

'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 E = LSearchValue, copy entire row to Sheet2
    If Range("E" & CStr(LSearchRow)).Value = LSearchValue Then

        'Select row in Sheet1 to copy
        Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
        Selection.Copy

        'Paste row into Sheet2 in next row
        Sheets("Sheet2").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("Sheet1").Select

    End If

    LSearchRow = LSearchRow + 1

Wend

'Position on cell A3
Application.CutCopyMode = False
Range("A3").Select

MsgBox "All matching data has been copied."

Exit Sub

Err_Execute: MsgBox "An error occurred."

End Sub`

Answer 1

Instead of using Select, you can simply stay what cell you're actually looking for as Vityata said.

All the selecting will slow you down.

Instead of:

Sheets("Sheet2").Select
Range("A5").Select
ActiveCell.Copy

You can simply do

Sheets("Sheet2").Range("A5").Copy

You may also consider using With statements if you're going to be referencing a sheet a lot.

With statements let you leave off parts of the string.

So you can simply say:

With Sheets("Sheet1")
    .Cells(1,1) = "Hi"    'Same as Sheets("Sheet1").Cells(1,1)
    .Cells(1,2) = "Hello" 'Same as Sheets("Sheet1").Cells(1,2)
End With

Just a few pointers - let me know if you have any questions.

Here's your code simplified.

Sub SearchForString()
Dim c, LSearchValue, LSearchRow, LCopyToRow, LastRow
On Error GoTo ErrHandle
LSearchValue = InputBox("Please enter a value to search for.", "Enter value")
LastRow = Sheets("Sheet1").Cells(Rows.CountLarge, "D").End(xlUp).Row
LSearchRow = 4
LCopyToRow = 2
For Each c In Sheets("Sheet1").Range("D" & LSearchRow & ":D" & LastRow)
    If c = LSearchValue Then
        c.EntireRow.Copy Sheets("Sheet2").Cells(LCopyToRow, "A")
        LCopyToRow = LCopyToRow + 1
    End If
Next c
Application.CutCopyMode = False
MsgBox "All matching data has been copied."
Exit Sub
ErrHandle: MsgBox "An Error Has Occured: " & Err.Description
End Sub

Sheet1 Input:

Sheet2 Output:

Answer 2

In general, from your screenshot, "Mailbox" is in column D, and you are checking column E. Write "Mailbox" in column E and it should work.

---

Nevertheless:

The problem is that you do not refer to the worksheet, thus VBA refers to the ActiveSheet. E.g., instead of:

Range("E" & CStr(LSearchRow)).Value = LSearchValue

you should write:

Worksheets(2).Range("E" & CStr(LSearchRow)).Value = LSearchValue

Or instead of:

While Len(Range("A" & CStr(LSearchRow)).Value) > 0

the code should be like this:

While Len(Worksheets(1).Range("A" & CStr(LSearchRow)).Value) > 0

Thus, try to rewrite the code, defining correctly the Range, Column, Rows, Cell with the corresponding Worksheet(1) and it should probably work. In general, using Select and ActiveCell is considered bad practice - How to avoid using Select in Excel VBA, but it is the first step, when you going from recording macros to VBA writing.