Combine Worsheets (Excel 03)

  • Thread starter Thread starter Havenstar
  • Start date Start date
H

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
 
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.
 
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
 
Back
Top