I have updated a complicated workbook and its macros. I want to provide a
macro that transfers the data from the old workbooks to the new version. The
old files may have 100 or more worksheets. That is not where I'm having
trouble. I have the transfer part working well but I want to make the process
simple for the user. The program switches back and forth between the old and
new creating a new worksheet in the new workbook and then transfers the data.

Right now the coding I have requires the user to enter the name of the old
file which would already be open. I would like Excel to search for the other
open file automatically.
After that I want the program to always select Sheet1 of the old file to
start. I thought this would be simple but I get an error when the program
gets to that line

sub XFERDATA

NEWFILE = ActiveWorkbook.Name
OLDFILE = InputBox("Enter old file name including .xls extension")
OLDSHEET = InputBox("ENTER NAME OF FIRST SHEET IN OLD WSWD")
Windows(OLDFILE).Activate <-- activates old file
Worksheets(OLDSHEET).Select <-- selects the sheet by name on old file

The above works ok but the program always starts from Sheet1 (by index) and
I would like it to eliminate the InputBoxes and to initially work like this:

< detect other open workbook from new one
OLDFILE = <name of other open workbook
Window(OLDFILE).Activate
Sheet1.Select <-- this does not work even when OLDFILE
is entered
thru the input box. It gets
an object error msg. I
have to use whatever the
sheet name changed to.
OLDSH = ActiveSheet.Name <-- then after Sheet1 is selected from the old
file
the actual name of the
sheet would be saved

Thanks for your help. This site has been great for solving problems. I think
there should be a simple solution but I sure can't find it.


Windows(NEWFILE).Activate
OLDFILE = Range("P4")

' OLDSH = Range("P5")
PREVSH = ""
SH1 = ActiveSheet.Name