Results 1 to 2 of 2

Macros Help!

Threaded View

  1. #1
    Registered User
    Join Date
    08-31-2011
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    1

    Macros Help!

    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!
    Last edited by mbarbee; 08-31-2011 at 04:44 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1