Excel issue

[Cpreston]

Hi

I am trying to run a macro (found on google)in excel 2010.

I have stepped into the code and it gets to point of going to sheet 2 (cell A2) and then jumps directly to the msgbox 'An Error Occurred'.
It finds and selects the first row, goes to sheet 2 and then the error occurred. I cannot see why this is happening (slight novice with code), any ideas please. (code as it is now below), Thanks

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 H = LSearchValue, copy entire row to Sheet2
If Range("H" & 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

 

[SkipVought]

hi,

Well if your sheet1 is set up properly, it will run to copy sheet1 row 4 to sheet2 row 2.

How have you set up sheet1?

 

[Cpreston]

Hi

I believe the sheet was set up correctly but we just solved this another way using a MS Query in the second sheet that linked to the main sheet.

I tried doing it by code as you can see but using MS Query in sheet 2 made it much easier.

Thanks for your help as always

 

[SkipVought]

ABSOLUTELY! Totally agree!

 

[SkipVought]

8:57 on MS Office
9:30 on VB
13:22 here on VBA

Quite a path to a solution.