error '1004' at run-time but works while stepping through with debugger?

[TheJustMan]

I am teaching myself VB using ms Excel 2010. I am trying to search through 7000+ rows of data for certain keywords, and then pasting these rows to another sheet. I have the code to where it works when I step through line by line with F8, but when I actually run the code I get "Run-time error '1004': Application-defined or object-defined error".

here is my code:

Private Sub SearchAndCopyCon()

    
    Dim countryColumn As Integer, lastColumn As Integer
    countryColumn = 8
    lastColumn = 29
    
    
    Dim ws As Worksheet
    Set ws = Worksheets(1)
    ws.Activate
    
    Dim RowRng As Range
    Dim currentRng As Range
    
    Set RowRng = ws.Range("C1").EntireColumn
    RowRng.Select
    
    
    While Not doneScanning
        Set currentRng = ActiveCell
        
        For Each currentRng In RowRng
            Select Case ActiveCell.Value
                Case "11C Crane - Rough Terrain"
            
                    Dim copyRange As Range
                    'MsgBox "" & ActiveCell.Address
                    'MsgBox "" & ws.Cells(ActiveCell.Row, countryColumn)
                    Set copyRange = ws.Range((ActiveCell.Address) & ":" & ws.Cells(ActiveCell.Row, lastColumn).Address)
                    
                    'MsgBox "" & copyRange.Address
                    
                    If ws.Cells(ActiveCell.Row, countryColumn) = "USA" Then
                        Dim pasteRange As String
                        pasteRange = copyRange.Address
                        copyRange.Select
                        With Selection
                            .Copy
                            Worksheets("USA").Activate
                            ActiveSheet.Range(pasteRange).Select
                            With Selection
                                .PasteSpecial (xlPaste)
                            End With
                            ws.Activate
                        End With
                    End If
                    If ws.Cells(ActiveCell.Row, countryColumn) = "Canada" Then
                        Dim pasteRange As String
                        pasteRange = copyRange.Address
                        copyRange.Select
                        With Selection
                            .Copy
                            Worksheets("Canada").Activate
                            ActiveSheet.Range(pasteRange).Select
                            With Selection
                                .PasteSpecial (xlPaste)
                            End With
                            ws.Activate
                        End With
                    End If
                
                'Add more cases here
                
                Case "TOTAL PRODUCTION - NORTH AMERICA"
                    doneScanning = True
                
            End Select
        
        Next currentRng
        
        ActiveCell.Offset(1, 0).Activate
        
    Wend




End Sub

My focus is on understanding why I am getting the error. If anyone is willing to take the time to explain techniques for making a search like this more efficient I would be more the happy to learn, but please don't feel obligated to.

Thanks for any and all help

 

[Andrew Poulsom]

On which line do you get the error?

 

[TheJustMan]

The .PasteSpecial (xlPaste) line

 

[Andrew Poulsom]

xlPaste isn't a VBA constant. Do you mean xlPasteValues? And you don't need the parentheses.

 

[TheJustMan]

ugh! such a minor mistake. Thanks ! it works now. I just changed it to xlPasteValues.

How do I flag a post as solved?