Hi Bernie

Thanks for your reply.


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
kk,

You could also automate it. Try the macro below. It will combine the
databases from all the sheets in a workbook, based on the field names in
row
1 and the key values in column A. Note that you would need to have the
same
value in cell A1 of both sheets: PRoduct and Model should be the same.

HTH,
Bernie
MS Excel MVP

Sub ConsolidateDatabasesMultiSheets()
Dim BaseBook As Workbook
Dim BaseSheet As Worksheet
Dim myBook As Workbook
Dim mySht As Worksheet
Dim myCell As Range
Dim myCell2 As Range
Dim myColumn As Integer
Dim myRow As Long
Dim FirstCopy As Boolean

FirstCopy = True

With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With

Set BaseSheet = Worksheets.Add
ActiveSheet.Name = "Combined"
For Each mySht In ActiveWorkbook.Worksheets
If mySht.Name < BaseSheet.Name Then
If FirstCopy Then
mySht.Cells.Copy BaseSheet.Range("A1")
FirstCopy = False
GoTo NextSheet:
End If
mySht.Activate
myRow = BaseSheet.UsedRange.Rows.Count + 1
For Each myCell In Intersect(Range("1:1"), _
ActiveSheet.UsedRange)
If myCell.Value < "" Then
If IsError(Application.Match(myCell.Value, _
BaseSheet.Range("1:1"), False)) Then
With BaseSheet.Range("IV1").End(xlToLeft)(1, 2)
.Value = myCell.Value
myColumn = .Column
End With
Else
myColumn = Application.Match(myCell.Value, _
BaseSheet.Range("1:1"), False)
End If

For Each myCell2 In Intersect(Range("A2:A65536"), _
ActiveSheet.UsedRange)
If IsError(Application.Match(myCell2.Value, _
BaseSheet.Range("A:A"), False)) Then
With BaseSheet.Range("A65536").End(xlUp)(2)
.Value = myCell2.Value
myRow = .Row
End With
Else
myRow = Application.Match(myCell2.Value, _
BaseSheet.Range("A:A"), False)
End If

BaseSheet.Cells(myRow, myColumn).Value = _
Cells(myCell2.Row, myCell.Column).Value
Next myCell2
End If
Next myCell
End If
NextSheet:
Next mySht

ActiveWorkbook.SaveAs Application.GetSaveAsFilename("Consolidated.xls")

With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With

End Sub


"kk" wrote in message
...
Hi

I have a workbook with 2 worksheet

Sheet 1:
Product CountQty
A 10
B 20

Sheet 2:
Model SysQty
A 10
C 30


Is there a way to use a function to consolidate two set of data from both
worksheet into a new sheet to

Product CountQty SysQty
A 10 10
B 20
C 30

Thanks!
kk