How to combine multiple workbooks to new one workbook in Excel 2010?

Dear all
Hope you are doing well. I have three workbooks. Every workbook has 50 person’s IT information. Those are CPU list, Monitor list, UPS list. I would like to create another workbook. Where all user’s IT information will be available. Like X user’s information required, under x information his CPU, monitor and UPS information link will be available. How to create link of the CPU, UPS monitor sheets with new workbook?

The code below will copy from three worksheets in each of three workbooks and paste the contents into a single worksheet in the master workbook. It assumes the code runs from the master workbook code module. The code also assumes that all four workbooks are open with the master being opened first. The first workbook opened is Workbooks(1), the othere will be (2), (3) and (4). If there are more than three sheets per workbook, you will need to substitute those names where in is noted in the code to edit the sheet name.
Code:
Sub collate()
Dim wb As Workbook, sh As Worksheet, sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, lr As Long, shAry
Set sh = ThisWorkbook.Sheets(1)
For i = 2 To 4
Set sh1 = Workbooks(i).Sheets(1) 'Edit sheet name
Set sh2 = Workbooks(i).Sheets(2) 'Edit sheet name
Set sh3 = Workbooks(i).Sheets(3) 'Edit sheet name
shAry = Array(sh1, sh2, sh3)
For j = LBound(shAry) To UBound(shAry)
lr = shAry(j).UsedRange.Rows.Count
shAry(j).Range("A2:A" & lr).EntireRow.Copy _
sh.Cells(Rows.Count, 1).End(xlUp)(2)
Next
Next
End Sub
Better code could be provided if better details were given.

Not sure if this is really what you're looking for but this is one that I found a while back that will let you select the workbooks that you want to combine and will copy them one after the other on a single sheet in a new workbook.

Sub mergebooks1sheet()
'
Dim varFilenames As Variant
Dim strActiveBook As String
Dim strSourceDataFile As String
Dim wSht As Worksheet
Dim allwShts As Sheets
Dim intResponse As Integer
Dim counter As Integer
Dim lRows As Long
'
intResponse = MsgBox("This macro will combine all data from all worksheets" & vbCrLf & "from all selected files to a single worksheet in a new workbook. Continue?", vbOKCancel, "Combine Files")
If intResponse = vbOK Then
Workbooks.Add
strActiveBook = ActiveWorkbook.Name
' Create array of filenames; the True is for multi-select
On Error GoTo exitsub
varFilenames = Application.GetOpenFilename(, , , , True)

counter = 1

' ubound determines how many items in the array
On Error GoTo quit
Application.ScreenUpdating = False
While counter <= UBound(varFilenames)

'Opens the selected files
Workbooks.Open varFilenames(counter)
strSourceDataFile = ActiveWorkbook.Name

Set allwShts = Worksheets
For Each wSht In allwShts
' Select Entire UsedRange from Source File
wSht.Activate
ActiveSheet.UsedRange.Select
Selection.Copy

' Find end of usedrange in destination file
Workbooks(strActiveBook).Activate
Range("A1").Select
ActiveSheet.UsedRange.Select
lRows = Selection.Rows.Count
ActiveCell.Offset(lRows, 0).Select

' Copy & Paste All including Formatting
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select

Next wSht
Workbooks(strSourceDataFile).Activate
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True

' displays file name in a message box
MsgBox varFilenames(counter) & " Has Been Processed", vbOKOnly + vbInformation, "File Processed"

'increment counter
counter = counter + 1

Wend

quit:
If Err <> 0 Then
MsgBox "An Error Occurred Trying to open the File. Please close any open Excel files and try again", vbOKOnly + vbExclamation, "File Open Error"
On Error GoTo 0
End If
End If
exitsub:
On Error GoTo 0
Application.ScreenUpdating = True

End Sub


I also have two others that may help, one will basically do the same thing but each original workbook will go in it's own sheet in the new workbook and I have one other that will consolidate all of the sheets in a workbook into one sheet. Let me know if you'd like me to post those as well to try out.

here is the simple way have a glimpse
www. extendoffice.com/documents/excel/456-combine-multiple-workbooks.html