Combine Worsheets (Excel 03)
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
"Havenstar" wrote:
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
|