I think this might help

Call GetContractFileName

Workbooks.Open
FileName:="G:\USER\Contracts\Data_Files\All_2004_C atalog.xls"
sFile = GetContractFileName
Sheets("Sheet1").Copy After:=Windows(sFile).Sheets(1)
Windows(sFile).Activate

are you sure about this next line, a sheet wouldn't normally have .xls
at the end

Sheets(sFile).Select


_______________________________________
Public Function GetContractFileName()
ContractFile = InputBox("Enter the Contract Number: ", "Contract File")
ContractNo=ContractFile
ContractFileName = ContractFile & ".xls"
End Function
_______________________________



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Emma" wrote in message
...
Here is what I am trying to do. We download information from our AS400
all
the time in particular a "contract file" I need to be able to create a
macro
that formats this contract file and performs several calculations on it.
I
am trying to do this via a macro button. I am having a problem when I
call
the file in my code.

Here is where I start having problems:


Call GetContractFileName

Workbooks.Open
FileName:="G:\USER\Contracts\Data_Files\All_2004_C atalog.xls"
Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Windows("80266-00-0.xls").Sheets(1) 'here
is where I need to use the variable "ContractFileName" I just don't know
how
to do it.
Windows("80266-00-0.xls").Activate 'here also
Sheets("80266-00-0.xls").Select 'and here too

_______________________________________
Public Sub GetContractFileName()
ContractFile = InputBox("Enter the Contract Number: ", "Contract
File")
ContractNo=ContractFile
ContractFile = ContractFile & ".xls"
End Sub
_______________________________


Can someone help? Pleeease!