Macro to copy data to a workbook from another workbook with a changing file name

This would be great, but you would have many workbooks that start with the first two words.
You could use a code such as

    Workbooks.Open Filename:= _
        "C:\Users\davesexcel\Downloads\" & Range("A1") & ".xls"

Then it would open the workbook with the name in the folder location with the name you had in Cell A1.

Change the folder location in the code.

Sure,
Try this to open the workbook.

Sub OpenWeeklyData()

    Dim File As String, Foldr As String

    Foldr = "C:\Users\davesexcel\Downloads\"
    File = Dir(Foldr & "Weekly Data*.xlsx")    '<= make sure ext is correct .xls 

    Workbooks.Open (Foldr & File)

End Sub

The file is coming across as read-only so that macro won't read it unless I save it and turn read-only off...more steps than I was hoping for. If the file is already open from Outlook, I shouldn't need the "Folder" command, right? I did try to take that code and apply it to the recorded macro that wasn't working either but had the beginning elements that I was looking for:

Sub Weekly_Data()

    Dim File As String
    File = "Weekly Data*.xls"
        
    Windows(File).Activate
    Sheets("Sheet1").Select
    Cells.Select
    Selection.Copy
    Windows("My Workbook.xlsm").Activate
    Sheets("Weekly Data").Select
    Cells.Select
    ActiveSheet.Paste
    Range("A1").Select
End Sub

I'm getting "Runtime Error 9: Subscript Out of Range" on the third line of code: Windows(File).Activate
I tried changing the syntax of the second and third lines to see if that was the problem but no go. Here are the variations I tried:

File = ("Weekly Data*.xls")
        
    Windows.Activate (File)

Any thoughts?

Maybe we need to loop through the open workbooks.

Sub Button1_Click()

    Dim Wb As Workbook

    For Each Wb In Workbooks

        If Wb.Name Like "Weekly Data" & "*" Then

            MsgBox "Found it " & Wb.Name

        End If

    Next Wb

End Sub

Sure thing,
Instead of copying every cell in the worksheet, it just copies A1 to the last used cell.
This is done without having to select the workbook or worksheet.

You can use the worksheet name instead of the number if you want.

These example codes could become usefull to you in the future
http://www.davesexcel.com/vbacodes.htm#363409847

Sub Weekly_Data2()

    Dim Rws As Long, Col As Integer, r As Range
    Dim Wb As Workbook, fRng As Range, Wsht As Worksheet

    For Each Wb In Workbooks

        If Wb.Name Like "Weekly Data" & "*" Then

            Set Wsht = Wb.Worksheets(1)

            With Wsht

                Set r = Range("A1")
                Rws = .Cells.Find(What:="*", After:=r, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                Col = .Cells.Find(What:="*", After:=r, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

                Set fRng = Range(.Cells(1, 1), .Cells(Rws, Col))    ' range A1 to last cell on sheet

                fRng.Copy Destination:=Workbooks("My Workbook.xlsm").Worksheets(1).Range("A1")

            End With

        End If

    Next Wb

End Sub