Selecting Sheet1 by index rather than sheet name

[Guest]

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

 

[Don Lloyd]

Hi,

Assuming that you have two workbooks open.
You can refer to the FIRST workbook OPENED as WorkBooks(1) and the other as
WorkBooks(2)

The first sheet of a workbook can always be referenced as Sheets(1)

Hope this helps,
Regards,
Don

 

[Guest]

Thanks Don, That worked great for selecting the workbook. Apparently as new
sheets are added to the workbook the index of the newest becomes (1). I need
to figure out a routine to scroll thru the sheets and detect the name of the
first sheet. They always begin with 1 - so I will test for the 1 and then
a space after it.
This is the code I wrote for picking the workbook and it was very simple
once you gave me the correct notation. Which most times is so simple but
just not aware of.

NEWFILE = ActiveWorkbook.Name <- where macro is run from
TEMP = Workbooks(1).Name
If TEMP = NEWFILE Then
OLDFILE = Workbooks(2).Name
Else
OLDFILE = Workbooks(1).Name
End If