Hi ilyaskazi,
Put the two books in a folder, paste this code in the book where you want the sheets imported to and run the code (Note: this is not restricted to importing sheets from one book - it will import all the sheets from all the books that you put in the folder).
Note that if you have any links in your worksheets, those links will now point to the original workbook[vba]Option Explicit
Sub AmalgamateBooks()
Dim ThisBook As Workbook, OtherBook As Workbook, SheetName$, i&, N&
Set ThisBook = ActiveWorkbook
Application.ScreenUpdating = False
With Application.FileSearch
.LookIn = ActiveWorkbook.Path
.FileName = "*.xls"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
If .FoundFiles(i) <> ThisWorkbook.FullName Then
Application.Workbooks.Open(.FoundFiles(i)).Activate
Set OtherBook = ActiveWorkbook
With OtherBook
For N = 1 To Sheets.Count
Sheets(N).Activate
SheetName = ActiveSheet.Name
Cells.Copy
With ThisBook
.Activate
Worksheets.Add After:=Sheets(Sheets.Count)
With .ActiveSheet
.Paste
.Name = SheetName & "1"
[A1].Select
End With
Application.CutCopyMode = False
End With
.Activate
Next N
End With
With OtherBook
.Close False
End With
End If
Next i
End If
End With
Sheet1.Activate
End Sub
HTH,
John