Run-time error '5': Invalid procedure call or argument

[VytautasM]

Good day,

Trying to adapt a code to suit my needs. But keep getting a Run Time Error:

The code compares two data ranges from different workbook docs., and copies the differences to a third workbook from which it runs. The error occurs on line: c.EntireRow.Copy rgPaste. From what I understand the destination range rgPaste = Nothing, but tried many different ways to define it with no luck. Any help is appreciated.

Original code: VBA - Compare values in one range to another range

Sub Pridėtosprekės()

    Dim Range1 As Range
    Dim Range2 As Range
    Dim c As Range
    Dim rgPaste As Range
    Dim strFolder1 As String
    Dim strFile1 As String
    Dim strFolder2 As String
    Dim strFile2 As String
    
    
    strFolder1 = Worksheets("Sheet1").Range("D3").Value
    strFile1 = Worksheets("Sheet1").Range("U3").Value
    
    strFolder2 = Worksheets("Sheet1").Range("D8").Value
    strFile2 = Worksheets("Sheet1").Range("U8").Value
    
    Workbooks.Open Filename:=strFolder1, UpdateLinks:=0, ReadOnly:=True
    Workbooks.Open Filename:=strFolder2, UpdateLinks:=0, ReadOnly:=True
    
    On Error Resume Next
    
    Set Range1 = Workbooks(strFile1).Worksheets("BAZINIS").Range("C9:C100")
    If Range1 Is Nothing Then
        MsgBox "No range selected. Ending program..."
        Exit Sub
    End If
    
    Set Range2 = Workbooks(strFile2).Worksheets("BAZINIS").Range("C9:C100")
    If Range1 Is Nothing Then
        MsgBox "No range selected. Ending program..."
        Exit Sub
    End If
    
    Set rgPaste = .Worksheets("Pridetos").Range("A8")
'   Truncate paste range to first cell
    Set rgPaste = rgPaste.Cells(1, 1)
    
    On Error GoTo 0
    
'   Move values from Range1 not in Range2
    For Each c In Range1.Cells
        If IsEmpty(c.Value) Then
        
        ElseIf Application.WorksheetFunction.CountIf(Range2, c.Value) = 0 Then
         c.EntireRow.Copy rgPaste
        
            Set rgPaste = rgPaste.Offset(1, 0)
        Else
        End If

    Next c
    
  Application.CutCopyMode = False
  
'Call Pašalintosprekės

           
End Sub

 

[Fluff]

Remove this line

On Error Resume Next

Then run your code, what happens?

 

[VytautasM]

Remove this line

On Error Resume Next

Then run your code, what happens?

Error "Run-time error '9': Subscript out of range" on line:

Set rgPaste = Worksheets("Pridetos").Range("A8")

 

[Fluff]

That would suggest that you don't have a sheet called Pridetos in the active workbook.
Assuming that the sheet is in the same workbook as the code, try

       Set rgPaste = ThisWorkbook.Worksheets("Pridetos").Range("A8")

 

[VytautasM]

That would suggest that you don't have a sheet called Pridetos in the active workbook.
Assuming that the sheet is in the same workbook as the code, try

       Set rgPaste = ThisWorkbook.Worksheets("Pridetos").Range("A8")

You are right. I set the destination in the opened workbook instead of the workbook with the code.

Changed code to:

Set rgPaste = ThisWorkbook.Worksheets("Pridetos").Range("A8")

Everything works as supposed to.

Thank you Fluff !

Thread can be closed.

 

[Fluff]

You're welcome & thanks for the feedback.