Put the following in a regular Module.

Public Sub CopySheet1()
Dim str1 As String
Dim iRtn As Integer
Dim wb As Workbook
Dim rng As Range

iRtn = MsgBox("Are you sure you want to copy to a non-empty sheet?",
vbYesNo)
If iRtn = vbNo Then Exit Sub
str1 = Application.GetOpenFilename("Excel files, *.xls")
Set wb = Workbooks.Open(str1)
Set rng = wb.Sheets("Sheet1").UsedRange
rng.Copy
ThisWorkbook.Activate
Sheets("Sheet1").Activate
Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
wb.Close
End Sub

Hth,
Merjet