[Randy L]

I have 2 sheets with similar data. Both are for orders placed by the
customer. But both sheets have different part numbers as well as the same
part numbers. What I am trying to do is pull from sheet 2 all the part number
orders and place on sheet 1.

For example Sheet 1:

Part No. 6/17 6/24 6/30
12334 2 4 3
12335 3 4 2
12337 2 2 0
18494 1 1 1

Sheet 2

Part No. 6/17 6/24 6/30
12331 1 1 1
12334 7 2 2
12337 2 8 4
17854 1 2 4


Would like to have the number of orders needed from sheet 2 sent to sheet 1,
appending them at the end of the columns with dates.

Part No. 6/17 6/24 6/30 6/17 6/24 6/30
12334 2 4 3 7 2
2
12335 3 4 2
12337 2 2 0 2 8
4
18494 1 1 1

Any help is most appreciated.

[joel]

Try this

Sub CombineSheets()

With Sheets("Sheet1")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
Sh1LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
Sh1NewCol = Sh1LastCol + 1
End With
With Sheets("Sheet2")
Sh2LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
.Range(.Range("B1"), .Cells(1, Sh2LastCol)).Copy _
Destination:=Sheets("Sheet1").Cells(1, Sh1NewCol)
RowCount = 2
Do While .Range("A" & RowCount) < ""
PartNo = .Range("A" & RowCount)
Set CopyRange = .Range(.Cells(RowCount, "B"), _
.Cells(RowCount, Sh2LastCol))
With Sheets("Sheet1")
Set c = .Columns("A").Find(what:=PartNo, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
.Range("A" & NewRow) = PartNo
CopyRange.Copy Destination:=.Cells(NewRow, Sh1NewCol)
NewRow = NewRow + 1
Else
CopyRange.Copy Destination:=.Cells(c.Row, Sh1NewCol)
End If
End With
RowCount = RowCount + 1
Loop
End With


End Sub

"Randy L" wrote:

I have 2 sheets with similar data. Both are for orders placed by the
customer. But both sheets have different part numbers as well as the same
part numbers. What I am trying to do is pull from sheet 2 all the part number
orders and place on sheet 1.

For example Sheet 1:

Part No. 6/17 6/24 6/30
12334 2 4 3
12335 3 4 2
12337 2 2 0
18494 1 1 1

Sheet 2

Part No. 6/17 6/24 6/30
12331 1 1 1
12334 7 2 2
12337 2 8 4
17854 1 2 4


Would like to have the number of orders needed from sheet 2 sent to sheet 1,
appending them at the end of the columns with dates.

Part No. 6/17 6/24 6/30 6/17 6/24 6/30
12334 2 4 3 7 2
2
12335 3 4 2
12337 2 2 0 2 8
4
18494 1 1 1

Any help is most appreciated.

[Don]

I would try a vlookup command. You can also create another sheet and put
both of them together (if the columns are the same) adding a new column to
identify each group , then do a pivot table

Data
Part No. location 6/17 6/24 6/30
12331 sheet2 1 1 1
12334 Sheet1 2 4 3
sheet2 7 2 2
12335 Sheet1 3 4 2
12337 Sheet1 2 2 0
sheet2 2 8 4
17854 sheet2 1 2 4
18494 Sheet1 1 1 1
Grand Total 19 24 17


"Randy L" wrote:

I have 2 sheets with similar data. Both are for orders placed by the
customer. But both sheets have different part numbers as well as the same
part numbers. What I am trying to do is pull from sheet 2 all the part number
orders and place on sheet 1.

For example Sheet 1:

Part No. 6/17 6/24 6/30
12334 2 4 3
12335 3 4 2
12337 2 2 0
18494 1 1 1

Sheet 2

Part No. 6/17 6/24 6/30
12331 1 1 1
12334 7 2 2
12337 2 8 4
17854 1 2 4


Would like to have the number of orders needed from sheet 2 sent to sheet 1,
appending them at the end of the columns with dates.

Part No. 6/17 6/24 6/30 6/17 6/24 6/30
12334 2 4 3 7 2
2
12335 3 4 2
12337 2 2 0 2 8
4
18494 1 1 1

Any help is most appreciated.