Macros Help!

**mbarbee** · 08-31-2011, 03:44 PM

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!