Manually: Use View | Windows } Arrange } tiled
Drag the tab of Sheet1 to the tab list of the second workbook
Then rename the new worksheet

Macro
Sub Macro1()
Workbooks("Book1").Sheets("Sheet1").Copy
Befo=Workbooks("Book2").Sheets(1)
Workbooks("Book2").Sheets("Sheet1 (2)").Select
Workbooks("Book2").Sheets("Sheet1 (2)").Name = "temp"
End Sub

best wishes
--
Bernard Liengme
Microsoft Excel MVP
people.stfx.ca/bliengme
email address: remove uppercase characters

"Varun" wrote in message
...
Guys,

How can I copy lets say sheet1 from excel file named one.xls and paste to
a
different excel file named two.xls wherein the sheet name also changes
from
sheet1 to Temp? Been try to do this but cannot get it to work.

Earlier in 2003, I got the code via recording a macro but in excel 2007 I
am
unable to figure out how to copy sheet using macro.

Thanks.