Condition to duplicate row of data

[georgecheng]

Dear bro and sister, I need your advice on the VBA script to duplicate the data when existing condition met.
In Column B, if there a "(SP)"" exist, I need the row to duplicate 3 more set of them. Example as below.

EXAMPLE
No	Code	Weight	ItemCode	date	Postage	Remark
99263435	,AA	5.76	431879438	12/1/2015	1.7	A
99263744	,RJ x 1	6.83	423610475	12/1/2015	0.6	B
99264668	,Bag, (SP)	4.95	438527536	12/1/2015	1.15	C
99270401	,BagA	4.95	438527536	12/1/2015	0	D
99271857	,PW17	1.35	412441505	12/1/2015	0	E
99274062	,LG28	8.89	429752744	12/1/2015	1.7	F
99276635	,LG20,V16, (SP)	9.32	429752744	12/1/2015	0.6	G
99277063	,LG20,V16	9.32	429752744	12/1/2015	0.6	H
99278220	,LG20,V16,BA-BBm, CD	14.27	429752744	12/1/2015	0.6	I
RESULT
No	Code	Weight	ItemCode	date	Postage	Remark
99263435	,AA	5.76	431879438	12/1/2015	1.7	A
99263744	,RJ x 1	6.83	423610475	12/1/2015	0.6	B
99264668	,Bag, (SP)	4.95	438527536	12/1/2015	1.15	C
9.9E+07	,Bag, (SP)	4.95	438527536	12/1/2015	1.15	C
9.9E+07	,Bag, (SP)	4.95	438527536	12/1/2015	1.15	C
9.9E+07	,Bag, (SP)	4.95	438527536	12/1/2015	1.15	C
99270401	,BagA	4.95	438527536	12/1/2015	0	D
99271857	,PW17	1.35	412441505	12/1/2015	0	E
99274062	,LG28	8.89	429752744	12/1/2015	1.7	F
99276635	,LG20,V16, (SP)	9.32	429752744	12/1/2015	0.6	G
9.9E+07	,LG20,V16, (SP)	9.32	429752744	12/1/2015	0.6	G
9.9E+07	,LG20,V16, (SP)	9.32	429752744	12/1/2015	0.6	G
9.9E+07	,LG20,V16, (SP)	9.32	429752744	12/1/2015	0.6	G
99277063	,LG20,V16	9.32	429752744	12/1/2015	0.6	H
99278220	,LG20,V16,BA-BBm, CD	14.27	429752744	12/1/2015	0.6	I

<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[georgecheng]

If the script can't be insert to row in-between, added to the last of the existing row will also be good. After that, i can use re-sort data for it. Thanks

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG20Feb57
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lst = Range("B" & Rows.Count).End(xlUp).Row
[COLOR="Navy"]For[/COLOR] n = Lst To 1 [COLOR="Navy"]Step[/COLOR] -1
    [COLOR="Navy"]With[/COLOR] Range("B" & n)
            [COLOR="Navy"]If[/COLOR] InStr(.Value, "(SP)") > 0 [COLOR="Navy"]Then[/COLOR]
                .EntireRow.Copy
                .EntireRow.Offset(1).Resize(3).Insert
            [COLOR="Navy"]End[/COLOR] If
       [COLOR="Navy"]End[/COLOR] With
 [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick