Microsoft Excel - Combining data from two excel workbooks into one workbook, Excel 2007

Asked By Lisa G on 11-May-11 06:38 PM
This question has been asked many times, in different ways ... and none of the answers help me. So, here we go, again.

Excel 2007

I have 2 excel workbooks. There is different information in each workbook, but it is about the same accounts. Both workbooks have some of the same information in the columns: Unit # : Record # : Name : Start Date : End Date.
Then .... in one workbook I have columns with billing information (approx 5 columns) and the other workbook has misc info (approx 5 columns). Each individual record is one row of data. I want to combine the information in to a separate workbook where it would have (accross one row, without duplicating data):
Unit # : Record # : Name : Start Date : End Date : billing 1 : billing 2 : billing 3 : misc 1 : misc 2: misc 3: ....etc.

Can anyone help me?
Thanks!
Jitendra Faye replied to Lisa G on 11-May-11 11:10 PM
Try this sample code-

[vba]Sub Merge2()

Dim shtOutput As Worksheet
Dim shtJob As Worksheet
Dim shtAge As Worksheet
Dim lngRow As Long
Dim rngFind As Range
Dim rngOutput As Range

Set shtOutput = ThisWorkbook.Worksheets(1)
shtOutput.Cells.Clear
shtOutput.Range("A1:F1") = Array("EE ID", "Name", "Age", "Reason", "Amt", "Job")

Set shtJob = Workbooks.Open("C:\temp\namejob.xls").Worksheets(1)
Set shtAge = Workbooks.Open("C:\temp\nameage.xls").Worksheets(1)

shtAge.Range("A2:E" & shtAge.Range("A1").End(xlDown).Row).Copy shtOutput.Range("A2")
shtAge.Parent.Close False

Set rngOutput = shtOutput.Range("B1").End(xlDown).Offset(1, 0)

lngRow = 2
With shtJob
Do While .Cells(lngRow, 2) <> ""

Set rngFind = shtOutput.Range("A:A").Find(.Cells(lngRow, 1).Value, , LookIn:=xlValues, lookat:=xlWhole)
If rngFind Is Nothing Then
' insert new information
rngOutput.Offset(0, 0) = .Cells(lngRow, 1) ' EE ID
rngOutput.Offset(0, 1) = .Cells(lngRow, 2) ' Name
rngOutput.Offset(0, 3) = .Cells(lngRow, 4) ' Reason
rngOutput.Offset(0, 5) = .Cells(lngRow, 3) ' Job

Set rngOutput = rngOutput.Offset(1, 0)
Else
rngFind.Offset(0, 0) = .Cells(lngRow, 1) ' EE ID
rngFind.Offset(0, 1) = .Cells(lngRow, 2) ' Name
rngFind.Offset(0, 3) = .Cells(lngRow, 4) ' Reason
rngFind.Offset(0, 5) = .Cells(lngRow, 3) ' Job
End If
lngRow = lngRow + 1
Loop
End With

shtJob.Parent.Close False

End Sub[/vba]

for more detail follow this-
http://www.ozgrid.com/forum/showthread.php?t=59005
Jitendra Faye replied to Lisa G on 11-May-11 11:13 PM

The following macro steps through all the worksheets and combines the data to a new worksheet it adds at the beginning of the workbook.

Sub Combine()
    Dim J As Integer

    On Error Resume Next
    Sheets(1).Select
    Worksheets.Add ' add a sheet in first place
    Sheets(1).Name = "Combined"

    ' copy headings
    Sheets(2).Activate
    Range("A1").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("A1")

    ' work through sheets
    For J = 2 To Sheets.Count ' from sheet 2 to last sheet
        Sheets(J).Activate ' make the sheet active
        Range("A1").Select
        Selection.CurrentRegion.Select ' select all cells in this sheets

        ' select all lines except title
        Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

        ' copy cells selected in the new sheet on last line
        Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
    Next
End Sub

When the macro is done, the first sheet in the workbook, named Combined, has all the data from the other worksheets. The other worksheets remain unchanged.