VBA that populates data on another spreadsheet from one piece of data - working but not if it's a linked data cell

[soopamuddle]

Hi everyone, would appreciate anyone's help with this. I'm pretty new to VBA but have managed to mangle together some bits and pieces to get something working for my needs. It's been working great until we've hit the problem of when we have linked data cells.

Here is our setup:

We have three spreadsheets. Log 1, 2, 3.

Log 1 has the main data reported on it. While that is being populated we initiate the link to Log 2 using the linked data cells so that log 2 automatically updates whenever something changes in log 1. That works perfectly for our needs.

Log 3 then contains only some of the rows from log 2, and has some extra columns needed. So what we do is copy the reference number from log 2, paste it into log 3, and it automatically pastes the rest of the data into the required columns (which are different locations just to make matter more complicated). We had this all figured out and it was working great, but only once log 2 was complete and we had "broken the data link". If the data link/linked cells is still live so to speak then it doesn't work. When we copy the reference number in to Log 3, it pastes "BLUE" for some random reason, and then the rest of the data doesn't copy.

Any help would be very much appreciated. This is the VBA code we have in log 3:

Option Explicit

Sub LookUp(id As String, i As Integer)
Dim myFile As Workbook
Dim b As Boolean
Dim ws As Worksheet
Dim AdminS As String ', id As String
Dim r As Range
'Dim i As Integer

    AdminS = Range("A1").Value ',<-------- SELECTS FILES FROM HERE
    'CheckS if file is open
    b = False
    For Each myFile In Workbooks
        If myFile.Name = AdminS Then
            b = True
            Exit For
        End If
    Next myFile
    

    If b = False Then
        On Error GoTo EH
    End If
    
    Set myFile = Workbooks(AdminS)
    
    With ThisWorkbook.ActiveSheet
    For Each ws In myFile.Worksheets
        Set r = ws.Range("B:B").Find(id, lookat:=xlWhole)
        If Not r Is Nothing Then
            .Range("A" & i) = ws.Range("B" & r.Row) ',<-------- TARGET SPREADSHEET FIRST SOURCE SS LAST
            .Range("B" & i) = ws.Range("C" & r.Row)
            .Range("C" & i) = ws.Range("D" & r.Row)
            .Range("D" & i) = ws.Range("E" & r.Row)
            .Range("E" & i) = ws.Range("G" & r.Row)
            .Range("F" & i) = ws.Range("F" & r.Row)
            .Range("G" & i) = ws.Range("H" & r.Row)
            .Range("H" & i) = ws.Range("I" & r.Row)
            .Range("I" & i) = ws.Range("J" & r.Row)
            .Range("J" & i) = ws.Range("K" & r.Row)
            .Range("K" & i) = ws.Range("L" & r.Row)
            .Range("L" & i) = ws.Range("M" & r.Row)
            .Range("M" & i) = ws.Range("N" & r.Row)
            .Range("O" & i) = ws.Range("P" & r.Row)
            .Range("P" & i) = ws.Range("Q" & r.Row)
            .Range("Q" & i) = ws.Range("T" & r.Row)
            .Range("S" & i) = ws.Range("AE" & r.Row)
            .Range("U" & i) = ws.Range("AK" & r.Row)
            .Range("AM" & i) = ws.Range("AO" & r.Row)
            .Range("AN" & i) = ws.Range("AT" & r.Row)
            .Range("AO" & i) = ws.Range("AQ" & r.Row)
            Exit For
        End If
    Next ws
    End With
    Exit Sub
EH:
    MsgBox "The Log could not be found, please ensure you have the file open"
End Sub

 

[soopamuddle]

Sorry to be impatient, if anyone has any idea it would be very much appreciated

 

[soopamuddle]

Ok so I’ve figured out that the random colour is actually the reference the data link suddenly jumping back a column in the referenced spreadsheet. If you look very carefully it pastes the correct data then quickly flicks to the column before. Have fixed that by using absolute references.

Have worked out the rest of the problem but not how to fix it. I think the issue is because the reference number isn’t being passed through the code as the ID, I think the formula is? But I don’t know how to fix that.