Emma,

It should be

Sheets("Sheet1").Copy After:=Workbooks(sFile).Sheets(1)

--

HTH

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


"Emma" wrote in message
...
I am getting an error he

Sheets("Sheet1").Copy After:=Windows(sFile).Sheets(1)

which says that

"Oject doesn't support this property or method"

Here is the code that I used:

Call GetContractFileName

Workbooks.Open
FileName:="G:\USER\Contracts\Data_Files\All_2004_C atalog.xls"
sFile = contractfilename
cntNo = ContractNo
Sheets("Sheet1").Copy After:=Windows(sFile).Sheets(1)
Windows(sFile).Activate
Sheets(cntNo).Select
_____________________________________
Public Function GetContractFileName()
ContractNo = InputBox("Enter the Contract Number: ", "Contract File")
contractfilename = ContractNo & ".xls"

End Function
__________________________________________
"Bob Phillips" wrote:

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!