0

I'm trying to loop to two different directories, compare the cell value in each file and try and paste if the value matches. However, there's an error in filename1 = Dir() line in this code. It's returning "Invalid Procedure or call argument" error. Please help.

Sub CompareAndPaste()
    Dim folder1 As String
    Dim folder2 As String
    Dim filename1 As String
    Dim filename2 As String
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim cell1 As Range
    Dim cell2 As Range
    
    folder1 = "Path1"
    folder2 = "Path2"
    
    ' Loop through all files in folder1
    filename1 = Dir(folder1 & "*.xlsx")
    Do While filename1 <> ""
        Set wb1 = Workbooks.Open(folder1 & filename1)
        Set ws1 = wb1.Sheets("Sheet1")
        
        ' Loop through all files in folder2
        filename2 = Dir(folder2 & "*.xlsx")
        Do While filename2 <> ""
            Set wb2 = Workbooks.Open(folder2 & filename2)
            Set ws2 = wb2.Sheets("Sheet2")
            
            ' Compare cell values in the two files
            Set cell1 = ws1.Range("H2")
            Set cell2 = ws2.Range("H2")
            If StrComp(cell1.Value, cell2.Value, vbTextCompare) = 0 Then
                ' If the cell values match, paste the data from file1 to file2
                ws2.Cells.Clear
                ws1.Cells.Copy ws2.Range("A1")
            End If
            
            ' Close the file2
            wb2.Close False
            filename2 = Dir
        Loop
        
        ' Close the file1
        wb1.Close False
        filename1 = Dir
    Loop
    MsgBox ("Completed the operation")
End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
  • https://stackoverflow.com/questions/41068315/excel-vba-using-multiple-instances-of-dir https://stackoverflow.com/questions/45909101/use-of-two-dir-vba – braX Feb 21 '23 at 02:47
  • Does this answer your question? [Use of two Dir VBA](https://stackoverflow.com/questions/45909101/use-of-two-dir-vba) – Ken White Feb 21 '23 at 02:48
  • Do you really want to compare each file in folder1 with every file in folder 2 ? The order of files returned by Dir is non-deterministic so you can't use it to reliably compare a sequence of files in one folder with the same sequence of files in another folder. – CDP1802 Feb 21 '23 at 10:49

0 Answers0