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

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

"Rich J" wrote in message
...
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

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



"Don Lloyd" wrote:

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

"Rich J" wrote in message
...
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