I have a program to automatically transfer data from an older version to an
updated file The Macro runs from the new file. Right now I have the user
enter the old filename and first sheet name by input boxes. This works well
but I would like VBA to detect the old file that would be already open and
put the name into a variable. The transfer always starts from Sheet1 of the
old file no matter what the name of the sheet has been changed to.

This works but not what I want:
OLDFILENAME = Inputbox("ENTER OLD FILE NAME")
FIRSTSHEETNAME = Inputbox("ENTER NAME OF FIRST SHEET IN OLD FILE")
Windows(OLDFILENAME).Activate
Sheets("FIRSTSHEETNAME").Select

This is what I would like

<macro to find other open workbook
OLDFILENAME = <detected workbook
Windows(OLDFILENAME).Activate
Sheet1.Select <-- This gives an error msg

or

Windows(OLDFILENAME).Activate
Sheet1.Activate <-- This activates Sheet1
of the new workbook
that the macro runs in

Thank you in advance