Find value from one workbook in another

[AndyEd]

I want to be able to copy a cell value from a workbook into a specific cell in another workbook. I've found some info on the internet but can't wrap my head around it and therefore adapt it to get it to work for me. It's so frustrating when you know in your head what you want to do, but can't create code to do it...

Example,
There are multiple similar Workbooks, i.e. "Memo x/2024", where x is a consecutive numerical value. Each "Memo x/2024" Workbook is contained within it's own folder, i.e. "1.2024 - Title summary", "2.2024 - Title summary" and so on.

There is a "Tracker" Workbook which is located at a fixed location, which lists each workstream by Row, i.e. Row2 contains data associated with "1.2024", Row3 with "2.2024" and so on.

In one such "Memo" Workbook, let's call it "Memo 1/2024" the reference number "1/2024" is a text value in Cell B11. There is also a date value in Cell B9.

When the user completes tasks associated with the folder documents, they input a date into the "1.2024 Memo" Workbook (Cell B9) which is the completion date. The reference number in Cell B11 is auto-populated when the Workbook is created.

I have coded for them to be able to send an email from the "Memo" Workbook to a team manager to notify them that the tasks have been completed and the files are ready for review. I would like to be able to send the date value from Cell B9 in the "Memo" Workbook to the appropriate Cell in the "Tracker" Workbook.

Whilst I know how to send the value from one to the other, and understand how to reference the appropriate Column using offset, I am encountering two problems,

1) How do I code to use the reference number of Cell B11 in the "Memo" Workbook, to locate the appropriate Row in the "Tracker" Workbook, searching through Range("B2:B")?
2) How can I update the "Tracker" Workbook regardless of whether it is open.

Any help would be very much appreciated.

 

[AndyEd]

Can anyone help with this?

 

[AndyEd]

Can it not be done?

 

[AndyEd]

Ok, I've got this which covers changes made within the same workbook, although I do have a couple of questions...

ThisWorkbook.Worksheets("Tracker").Activate

        Cells.Find(What:=Me.cboSelectReferenceNumber.value, After:=ActiveCell, LookIn:=xlFormulas2, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
        
        With ActiveCell
            .Offset(0, 9).value = Me.cboName.value
        End With

It seems as though I have to Activate the "Tracker" Worksheet? If the code is initiated whilst it is not the active worksheet, the code stalls.

And the 'find' code selects (Activate) the found Cell, which seems appropriate, as I then have to reference that Cell to offset to the Cell where I want to put the data attributed to cboName. So far so good.....?

Is this the best, or only method? I thought 'Selecting' Cells etc. was bad, but perhaps I'm getting confused?

I also have to reference a Cell in ThisWorkbook.Worksheets("Tracker") from another Workbook, to copy across information from one to the other, to the appropriate Cell. Will I have to Open ThisWorkbook.Worksheets("Tracker") to successfully execute the code, and then Close it again?

What if ThisWorkbook.Worksheets("Tracker") is already open?

Sorry if these questions are very basic

 

[AndyEd]

So, I've defined the value in the workbook that the code is being executed. The destination workbook is then opened on the relevant worksheet. I'm then getting the following error when it comes to finding the value.

Code so far.

Dim Ref As String
    Ref = ThisWorkbook.Worksheets("Memo").Range("C11")
    Workbooks.Open "filepath"
    Worksheets("Tracker").Range("A1")

I'm getting this error,

Run-time error '1004':
Activate method of Range class failed

At this point,

Cells.Find(What:=Ref, After:=ActiveCell, LookIn:=xlFormulas2, LookAt:=xlPart, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

I don't know why?