Upload data to Specified columns in Closed Workbook - if name in column A matches

[paula.mccall]

Good evening.

I am needing some help please. I have attached two separate workbooks ("Templates") --Monitoring Report and Analytical Data.

1. I upload the analytical data to the Analytical Template (which does have formulas in columns C & E - S).

What I need is:

2. I need to copy the final data from columns B - S of the Analytical Data WB - ABC Stage WS
and Paste that data (as numbers, not as formulas) to columns H - Z of the Monitoring Report WB - Treat Summary WS
a. There may be data in columns B - G of the Monitoring Report, which will need to stay intact (as is)
b. The pasted data will need to copy to the row that matches the names from column A of the Analytical Data to column A of the Monitoring Report.


Example:
Column A of the Monitoring Report - Treat Summary WS (beginning at Cell A9) has a list of 5 well names
AAA-1, AAA-2, LuLu, 1-12 5WA, 1-12 3BS

Column A of the Analytical Report - ABC Stage WS (beginning at Cell A2) has a list of 4 well names
AAA-1, AAA-2, 1-12 5WA, 1-12 3BS

I need the data from the AAA-1 data in the Analytical Report to upload the the row that has AAA-1 of the Monitoring Report


I tried to write the following macro, but it is not running the way I had hoped:

Sub Analytical()

'Dim WM As Workbook

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim srcWS As Worksheet, desWS As Worksheet, RngList As Object
Dim LastRow As Long, bottomA As Long, Rng As Range
Set srcWS = ThisWorkbook.Sheets("ABC Stage")
LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                
                
Workbooks.Open "C:\Users\paulam\Documents\Monitoring Report_2020.T-1.xlsm"


Set desWS = Sheets("Treat Summary") ''

bottomA = desWS.Range("H" & desWS.Rows.Count).End(xlUp).Row

desWS.Range("H9:Z" & bottomA).ClearContents

Set RngList = CreateObject("Scripting.Dictionary")
For Each Rng In srcWS.Range("A2:A" & LastRow)
    If Not RngList.Exists(Rng.Value) Then
        RngList.Add Rng.Value, Rng.Row
    End If
Next Rng

For Each Rng In desWS.Range("H9", desWS.Range("H" & desWS.Rows.Count).End(xlUp))
    If RngList.Exists(Rng.Value) Then
        Intersect(srcWS.Rows(RngList(Rng.Value)), srcWS.Range("B:B,C:C,D:D,E:E,F:F,G:G,H:H,I:I,J:J,K:K,L:L,M:M,N:N,O:O,P:P,Q:Q,R:R,S:S,T:T")).Copy
                
    desWS.Cells(Rng.Row, 2).PasteSpecial xlPasteValues
    End If
Next Rng
RngList.RemoveAll
LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row


For Each Rng In desWS.Range("A9:A" & bottomA)

    If Not RngList.Exists(Rng.Value) Then
        RngList.Add Rng.Value, Nothing
    End If
Next Rng
For Each Rng In srcWS.Range("A2", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp))
    If Not RngList.Exists(Rng.Value) Then
Intersect(srcWS.Rows(Rng.Row), srcWS.Range("A:A,B:B,C:C,D:D,E:E,F:F,G:G,H:H,I:I,J:J,K:K,L:L,M:M,N:N,O:O,P:P,Q:Q,R:R,S:S,T:T")).Copy

desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    End If
Next Rng
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

I appreciate your help and time in resolving this request.

Kindest Regards,
Paula