Combine Worsheets (Excel 03)

[Havenstar]

Hi,

How can I combine (2) worksheets that have different data being pulled from
and ODBC into one Worksheet based on an Order Number?

Sheet 1
A1 B1 C1 D1
Order Nbr Ship Date Customer Description

Sheet 2
A1 B1
Order Nbr Contract Price

This is to be copied into a 3rd sheet combing all Data
A1 B1 C1 D1 E1
Order Nbr Ship Date Customer Description Contract Price

Any help would be appreciated.

Thank you,
Havenstar

 

[Luke M]

Sounds like you will want to use the VLOOKUP command. Something like
=VLOOKUP(A2,Sheet2!A2:B100,2,FALSE)
placed in E2 of sheet 3 would help you match up your data.

 

[Joel]

Try this code

Sub CombineSheets()

'clear sheet 3
Sheets("Sheet3").Cells.ClearContents

'Copy sheet 1 to Sheet 3
Sheets("Sheet1").Cells.Copy _
Destination:=Sheets("Sheet3").Cells

'Add Missing Header to shbeet 3
Sheets("Sheet3").Range("E1") = "Contract Price"
LastRow = Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1

'merge Sheet 2 in to sheet 3
With Sheets("Sheet2")
RowCount = 2
Do While .Range("A" & RowCount) <> ""
OrderNbr = .Range("A" & RowCount)
Price = .Range("B" & RowCount)
With Sheets("Sheet3")
Set c = .Columns("A").Find(what:=OrderNbr, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
'Order Number not found on sheet 2, add to new row
.Range("A" & NewRow) = OrderNbr
.Range("E" & NewRow) = Price
NewRow = NewRow + 1
Else
.Range("E" & c.Row) = Price
End If
End With

RowCount = RowCount + 1
Loop

End With

End Sub