Ok, Varun, this seems to be working:
Sub CCClick()
Dim ddifn As String
Dim ddifnWkBk As Workbook
ddifn = Application.GetOpenFilename(FileFilter:="Excel Files, *.xls", _
Title:="Please select layer stackup file received from fab shop")
Set ddifnWkBk = Workbooks.Open(ddifn, , ReadOnly)
Sheets("Sheet1").Select
Cells.Select
Selection.Copy
ddifn = Application.GetOpenFilename(FileFilter:="Excel Files, *.xls", _
Title:="Please select layer stackup file received from fab shop")
Set ddifnWkBk = Workbooks.Open(ddifn, , ReadOnly)
Sheets("Sheet2").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
End Sub

I hope I got it right. All I did was fire the macro, click on Book1, then
click on Book2. That's pretty much it. Watch the Workbook names and Sheet
nsmes; change to suit your specific needs.

HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Varun" wrote:

Hi Bernard,

Can you please once again repeat the process? When I click View in Excel
2007, what do I do next (I see Arrange All and some other options and don't
see Windows or Arrange or Tiled) so I am unable to record the macro.

Help is appreciated.

"Bernard Liengme" wrote:

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.