Excel select and copay and past to another sheet

[Cpreston]

Hi

I have some code which from an input selection should copy and paste into another sheet all the rows it finds to another sheet.
The code is working so far then gives me this message I am getting Microsoft Excel: "Select method of range class failed" The code is as below ad I failry new to coding, please could someone advise how the code should look, 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 Sheet3 in next row
Sheets("Sheet3").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

' Copy row from current sheet into Sheet3
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Copy Sheets("Sheet3").Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow))

'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 Err.Description

End Sub

 

[SkipVought]

Hi,

Assuming you want to copy to Sheet2.

Note that Sheet1 is the sheet CodeName as opposed to the Sheet Name (tab name).
No Select required!

Sheet1.Rows(LSearchRow).Copy Sheet2.Cells(NextRow, "A")

 

[Andrzejek]

Cpreston, please use

 tag[/b] to present / format your code.  It is a lot eaier to read.

Have fun.

---- Andy

[i]A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.[/i]

 

[strongm]

To avoid any duplication of effort, please note that the OP has already been given advice and guidance in thread222-1747922, before more correctly posting in this forum.

 

[MigrantFirmWorker]

I see that your row index variables are defined as Integer. Integer is limited to a max value of 32,767. If you have more rows than this it will fail. Use Long for row indices.

Dim LSearchRow As Long
Dim LCopyToRow As Long

Chris

-------------------------------------------------------------
"Don't be deceived. We're all temporary employees.