Code unable to handle a certain condition..why?

[ParanoidAndroid]

Hi Guys

I have code which goes through each row in sheet "compile" and looks for a certain condition in column L from which it will cut and paste those rows where the condition is true into the appropriate sheet...

However sometimes the code crashes when a certain condition exists..

I'm theorising that my code expects a string value so crashes if otherwise

Whats in column L is a formula that reads from another spreadsheet..So sometimes you dont get a true value but rather #N/A - I dont have a problem with this but the code seems to?

Is this because i've defined the Dim as Integer? what should it be if so?

 Sub Time()

Dim LSearchRow As Integer
    Dim LCopyToRow As Integer
    
    Sheets("Compile").Activate
    On Error GoTo Err_Execute
    'Start search in row 4
    LSearchRow = 2
    '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 J = "Central", copy entire row to Central
        If Range("L" & CStr(LSearchRow)).Value = "Central " Then
            'Select row in Sheet1 to copy
            Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
            Selection.Copy
            'Paste row into Sheet2 in next row
            Sheets("Central").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("Compile").Select
        ElseIf Range("L" & CStr(LSearchRow)).Value = "Markets" Then
            'Select row in Sheet1 to copy
            Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
            Selection.Copy
            'Paste row into Sheet2 in next row
            Sheets("Markets").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("Compile").Select
        
                              
        End If
        LSearchRow = LSearchRow + 1
    
     Wend
     'Position on cell A3
     Application.CutCopyMode = False
          
     Exit Sub
Err_Execute:
    MsgBox "An error occurred."
    
    End Sub<!-- / message --><!-- sig -->
__________________

 

[AlphaFrog]

Both of these worked for me...
Destination:=Sheets("Central").Range("A2")
Destination:=Sheets("Central).Range("G" & LastRowCount + 1)

Can you better explain what exactly happens when you say...
"the .range(A2) in the destination wont seem to work?"
...does it error?, copy the wrong values, what?

This should paste to the Next empty cell in column G...

.Offset(1, -11).Resize(.Count - 1, 10).SpecialCells(xlCellTypeVisible).Copy _
      Destination:=Sheets("Central").Range("G" & Rows.Count).End(xlUp).Offset(1)

 

[ParanoidAndroid]

Thanks Alpha

Your code works a treat...My code actually worked - except i chaged some conditions for testing purposes and got myself flustered...

Thanks for your help. Much appreciated

 

[ParanoidAndroid]

Hi Alpha

I've been using your code very successfully until now. However ive come across a problem.

It seems if you autofilter and then copy and there is only one condition that matches (ie..meaning only 1 row is copied) then the code will not work?

Heard of this? how do i fix it?