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