Copy range of cells from one sheet to another

[audiorij]

Using Microsoft Office Professional Plus 2010

I am working with a macro that looks to see which rows of data contain "Yes" in column F and then it copes those rows to a new sheet. Everything works fine, except I would like to change it to only copy the data in columns A through N and not the entire row. Reason being I would like it to ignore column M on the new sheet.

This is what I am using;

Sub Search_For_String()


Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute

Sheets("Data").Select

'Start search in row 2
LSearchRow = 2

'Start copying data to row 8 in Investigations (row counter variable)
LCopyToRow = 8

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

'If value in column F = "Yes", copy entire row to Investigations
If Range("F" & CStr(LSearchRow)).Value = "Yes" Then

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

'Paste row into Investigations in next row
Sheets("Investigations").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Data to continue searching
Sheets("Data").Select

End If

LSearchRow = LSearchRow + 1

Wend

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

Sheets("Investigations").Select

MsgBox "All matching data has been copied."



Exit Sub

Err_Execute:
MsgBox "An error occurred."

End Sub

[Mumps1]

Try:

Please Login or Register  to view this content.

[audiorij]

I am getting an error. It might be because of which sheet is active? I am starting on the "Investigations" sheet via a control button that runs the macro. It is supposed to go back to "data" sheet and grab the data and return back to "Investigations" sheet and paste it there.

[Mumps1]

Try:

Please Login or Register  to view this content.