I'm trying to create a macro that will copy and paste rows onto a new spreadsheet when certain values are displayed.
If column G has a number that is greater than 30 or less than 10, I would like to copy the row that the cell is in and paste it onto a different sheet in my workbook.
I have the following code in my visual editor already but it will not copy anything over.
Sub SearchForString()
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
On Error GoTo Err_Execute
'Start search in row 2
LSearchRow = 2
'Start copying data to row 2 in Sheet3 (row counter variable)
LCopyToRow = 2
While Len(Range("A" & CStr(LSearchRow)).Value) > 0
'If value in column G > "30", copy entire row to Sheet3
If Range("G" & CStr(LSearchRow)).Value > "30" Or Value < "10" 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
'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
What am I doing wrong?
Thank you!
Bookmarks