There is probably no "Sheet1" in the workbook. You will also have a problem
if the spreadsheet you want to open does not exist. You can modify your code
as follows:

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

or use this code... which will let you know if the file does not exist...

Private Const m_cPath As String = "C:\"

Private Function GetFile() As String
GetFile = InputBox("Enter the Contract Number: ", "Contract File") &
".xls"

End Function

Private Sub Test()
Dim wbkCopyTo As Workbook
Dim shtToCopy As Worksheet

On Error Resume Next
Set wbkCopyTo = Workbooks.Open(m_cPath & GetFile)
On Error GoTo 0
If wbkCopyTo Is Nothing Then
MsgBox "Workbook not found.", vbCritical, "File Error"
Else
Set shtToCopy = ThisWorkbook.Sheets(1)
shtToCopy.Copy wbkCopyTo.Sheets(1)
wbkCopyTo.Activate
End If
End Sub




"Emma" wrote:

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!