Hi,

I have a set of data in 3 spreadsheets and needs to be combined into 1. Each
spreadsheet, col B-E is staff details (emp no, name..etc). Data contains in
column F onwards are different.

ie:
Sheet 1 : heading of Col F = Training A, Col G = Training B
Sheet 2 : heading of Col F = Training C, Col G = Training D
Sheet 3 : heading of Col F = Training E, Col G = Training F

Now, How do I combine all the Training A - F to same row in a sheet?


Appreciate if someone could give me a shortcut/formula to do it...

Thanks a zil..
Jessie

Try this code

Sub combinesheets()

Set Sumsht = Sheets.Add(after:=Sheets(Sheets.Count))
Sumsht.Name = "Summary"


'Copy sheet 1 to Summary sheet
Sheets("Sheet1").Cells.Copy _
Destination:=Sumsht.Cells
'Get Last Row of data
LastRow = Sumsht.Range("B" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1


DestCol = 8 'col H
'add sheets 2 and 3 to summary sheet
For ShtNum = 2 To 3
RowCount = 2
Set Sht = Sheets("Sheet" & ShtNum)
Do While Sht.Range("B" & RowCount) < ""
ID = Sht.Range("B" & RowCount)
'Check if ID Number exist in Summary
Set c = Sumsht.Columns("B").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
Sht.Range("A" & RowCount & ":E" & RowCount).Copy _
Sumsht.Range("A" & NewRow)
Sht.Range("F" & RowCount & ":G" & RowCount).Copy _
Sumsht.Cells(NewRow, DestCol)
NewRow = NewRow + 1
Else
Sht.Range("F" & RowCount & ":G" & RowCount).Copy _
Sumsht.Cells(c.Row, DestCol)
End If
RowCount = RowCount + 1
Loop
DestCol = DestCol + 2
Next ShtNum
End Sub


"Jessie" wrote:

Hi,

I have a set of data in 3 spreadsheets and needs to be combined into 1. Each
spreadsheet, col B-E is staff details (emp no, name..etc). Data contains in
column F onwards are different.

ie:
Sheet 1 : heading of Col F = Training A, Col G = Training B
Sheet 2 : heading of Col F = Training C, Col G = Training D
Sheet 3 : heading of Col F = Training E, Col G = Training F

Now, How do I combine all the Training A - F to same row in a sheet?


Appreciate if someone could give me a shortcut/formula to do it...

Thanks a zil..
Jessie