"An Error Occured" with a copy paste macro and recommendations

[raramachine]

Hello, I'm quite new at this, so please forgive any lack of knowledge.

I'm trying to get a macro to search for a term in a column (in this case, Camacho Services and column D) and when found, copy the row that has that data to another sheet. As I am a newbie, I modified the code from elsewhere, though I think I have changed the appropriate variables.

Whenever I run the code though, I get the error text. I've tried several permutations, but it there is still something going wrong.

The code is:


Sub SearchForString()

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 Sheet4 (row counter variable)
LCopyToRow = 2

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

'If value in column D = "Camacho Services", copy entire row to Sheet4
If Range("D" & CStr(LSearchRow)).Value = "Camacho Services" Then

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

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

Can anyone advise? Did I fail to change some variables pertinent to my desired search term or column? Did I accidentally mess something else up?


Also, I hope to become much better at this. Does anyone have tutorial recommendations?
Thanks so much.

 

[L. Howard]

Okay, and with two IF's...

If column B is available to run the TRUE formula code on... (another row could be used if needed)
If you want the "search word" rows copied to sheet 4...

Then this takes about 1 second on my computer with 1200 rows on sheet 1, copied about 300+ rows with the "search word" to sheet 4 starting in D4. (Copies column D to column O for each row)

Howard

Option Explicit

Sub Formula_Way_Copy()

  Dim lRowCount As Long
  lRowCount = Cells(Rows.Count, "D").End(xlUp).Row
  
Application.ScreenUpdating = False

  With Range("B4").Resize(lRowCount)
    .Formula = "=IF(ISNUMBER(SEARCH(""Camacho Services"",D4))=FALSE,"""",TRUE)"
    .AutoFilter Field:=1, Criteria1:="TRUE"
     Range("B4").Offset(, 2).Resize(lRowCount, 12).Copy Sheets("Sheet4").Range("D4")
    .AutoFilter
    .ClearContents
  End With
  
Application.ScreenUpdating = True
  
End Sub