Hello,
I am trying to make a macro that will copy and paste data from specific cells in two sheets of one workbook to specific cells of one sheet in another workbook, then that process should repeat for all the sheets in each of the workbooks.
What I have so far seems to have a problem somewhere as it does not do anything I can see (does not do the copy/paste part). I may have done the loop wrong or the file navigation part wrong, but I do not know where the error is as it compiles and runs.

The sheets in the destination workbook (where the values are being pasted) are "XRD-A", "XRD-B", "XRD-C", "XRD-D", "XRD-E", "XRD-F", "XRD-G", "XRD-H", "XRD-I", "XRD-J".
The sheets in where the data is at are "A-G", "B-G", "C-G", "E-G", "F-G", "G-G", "H-G", "I-G", "J-G", "A-N", "B-N", "C-N", "E-N", "F-N", "G-N", "H-N", "I-N", "J-N"

The data from "A-G" and "A-N" are supposed to be pasted in "XRD-A" and the same with B and so on...

Here is what I currently have:
    Dim RunNo As String, strPath As String, SourceFile As String
    Dim wsWafer As Worksheet
    
    RunNo = Mid(ThisWorkbook.Name, 6, 6)
    Name = ThisWorkbook.Name
    
    For Each wsWafer In Sheets(Array("XRD-A", "XRD-B", "XRD-C", "XRD-D", "XRD-E", "XRD-F", "XRD-G", "XRD-H", "XRD-I", "XRD-J"))
    
        strPath = "C:\X'Pert Data\Wafers\W" & RunNo & "\WaferXRDAnalysis" & RunNo
        SourceFile = Dir(strPath)
        If SourceFile <> "" Then
            Do
                With Workbooks.Open(Filename:=SourceFile)
                    Range(.Sheets("A-G").Range("P3"), .Sheets("A-G").Range("P3")).Copy Destination:=wsWafer.Range("I23")
                    Range(.Sheets("A-N").Range("P3"), .Sheets("A-N").Range("P3")).Copy Destination:=wsWafer.Range("I93")
                    
                    .Close False
                End With
                SourceFile = "StopThisLoop"
            Loop While SourceFile <> StopThisLoop
        End If
    Next wsWafer
Which part of the code is incorrect? Should I be doing this in a different way?
Please help me figure this out.