Solved insert row automatically and running sequence

August 14, 2015 at 00:01:06
Specs: Windows 7
hi,

i'm now able to insert row automatically. however i do not know how i can insert together with sequence number.
For example:

part no.	qty/pallet	Vol/week	Total lot	CW	year	Series
WRS-C	         100	            200	            2	        40	2015	   1
WRS-F	          20	            100	            5	        40	2015	   1

and when we click button, it should be

       part no.	qty/pallet	Vol/week	Total lot	CW	year	Series
WRS-C		100		200		2		15	2015	1
WRS-C		100		200		2		15	2015	2
WRS-F		20		100		5		15	2015	1
WRS-F		20		100		5		15	2015	2
WRS-F		20		100		5		15	2015	3
WRS-F		20		100		5		15	2015	4
WRS-F		20		100		5		15	2015	5

my macro;

Private Sub CommandButton1_Click()
'Determine last Row with data in Column D
  lastRw = Cells(Rows.Count, "D").End(xlUp).Row
'Loop through rows in reverse order
    For rw = lastRw To 5 Step -1
'If Column E > 0, insert Rows and copy data
     If Cells(rw, "D") > 0 Then
       For newRw = 1 To Cells(rw, "D") - 1
'Insert/Copy rows
         Cells(rw, "D").EntireRow.Copy
         Cells(rw, "D").EntireRow.Insert shift:=xlDown
       Next
     End If
    Next

End Sub

message edited by eida


See More: insert row automatically and running sequence

Report •

#1
August 14, 2015 at 02:45:15
i tried to insert below formula:

Private Sub CommandButton1_Click()
'Determine last Row with data in Column D
  lastRw = Cells(Rows.Count, "D").End(xlUp).Row
'Loop through rows in reverse order
    For rw = lastRw To 5 Step -1
    seq = 1
'If Column E > 0, insert Rows and copy data
     If Cells(rw, "D") > 0 Then
       For newRw = 1 To Cells(rw, "D") - 1
       
'Insert/Copy rows
         Cells(rw, "H") = seq
         Cells(rw, "D").EntireRow.Copy
         Cells(rw, "D").EntireRow.Insert shift:=xlDown
         
         seq = seq + 1
         
       Next
     End If
    Next

Resulted:

part no.	qty/pallet	Vol/week	Total lot	Plant	CW	year	Series
WRS-C		100		200		2			15	2015	1
WRS-C		100		200		2			15	2015	1
WRS-F		20		100		5			15	2015	4
WRS-F		20		100		5			15	2015	4
WRS-F		20		100		5			15	2015	3
WRS-F		20		100		5			15	2015	2
WRS-F		20		100		5			15	2015	1


Report •

#2
August 14, 2015 at 02:54:53
i hv able to make it sequence. im not if it is the best solution or not.

Private Sub CommandButton1_Click()
'Determine last Row with data in Column D
  lastRw = Cells(Rows.Count, "D").End(xlUp).Row
'Loop through rows in reverse order
    For rw = lastRw To 5 Step -1
    seq = 1     ' for sequence number
'If Column E > 0, insert Rows and copy data
     If Cells(rw, "D") > 0 Then
       For newRw = 1 To Cells(rw, "D") - 1
       
'Insert/Copy rows and sequence number
         Cells(rw, "H") = seq
         Cells(rw, "D").EntireRow.Copy
         Cells(rw, "D").EntireRow.Insert shift:=xlDown
         
         seq = seq + 1
         Cells(rw, "H") = seq
         
       Next
     End If
    Next

my result:

part no.	qty/pallet	Vol/week	Total lot	Plant	CW	year	Series
WRS-C		100		200		2			15	2015	2
WRS-C		100		200		2			15	2015	1
WRS-F		20		100		5			15	2015	5
WRS-F		20		100		5			15	2015	4
WRS-F		20		100		5			15	2015	3
WRS-F		20		100		5			15	2015	2
WRS-F		20		100		5			15	2015	1
WRS-z		80		240		3			15	2015	3
WRS-z		80		240		3			15	2015	2
WRS-z		80		240		3			15	2015	1
WRS-a		100		100		1			15	2015	1

message edited by eida


Report •

#3
August 14, 2015 at 06:52:26
✔ Best Answer

Report •
Related Solutions


Ask Question