Example:
Sheet1
Part no. Location Destination
1 A1 CE
2 A2 CE
3 A3 CE

Sheet2

Part no. Location Destination
1 A1 U2
2 A5 U2
4 A3 U2

Sheet3
Part no. Location Destination
1 A1 J
2 A5 J
5 A3 J

and continue...as many sheets.

End result as below
Sheet1 (after combine all)
Part no. Location Destination
1 A1 Com
2 A2 CE
2 A5 CE,J
3 A3 CE
4 A3 U2
5 A3 J

Currently what I did is to cut all data in all sheets and paste to
sheet1,arrange accordingly and delete certain rows that common using
true and false formula.
Pls help how to do it short cut in VBA.

Hi Genius,

To create your summary sheet, see Ron de Bruin's
sample code at:


Merge cells from all or some worksheets
into one Master sheet
http://www.rondebruin.nl/copy2.htm

The suggested code may be adapted to effect a final
sort operation on the first data column.

To obtain the sort code, turn on the macro recorder
while you perform the operation manually.

If you experience problems in adapting / incorporating
the resultant code, post back indicating your specific
problems.


---
Regards,
Norman



"geniusideas" wrote in message
oups.com...
Example:
Sheet1
Part no. Location Destination
1 A1 CE
2 A2 CE
3 A3 CE

Sheet2

Part no. Location Destination
1 A1 U2
2 A5 U2
4 A3 U2

Sheet3
Part no. Location Destination
1 A1 J
2 A5 J
5 A3 J

and continue...as many sheets.

End result as below
Sheet1 (after combine all)
Part no. Location Destination
1 A1 Com
2 A2 CE
2 A5 CE,J
3 A3 CE
4 A3 U2
5 A3 J

Currently what I did is to cut all data in all sheets and paste to
sheet1,arrange accordingly and delete certain rows that common using
true and false formula.
Pls help how to do it short cut in VBA.