VISHAL120
04-23-2021, 06:50 AM
Hi everyone,
I am actually using this below code to duplicate rows based on the cell value. Which in this case is the column AC which represents number of operators.
Here is the code I am using which works perfectly to duplicate the rows based on the Column AC[ number of operators].
Code
Sub populate_operations_sequence()
Dim lngRow As Long
'start_Time = Time
Dim intInsertRows As Integer
Transfer_Header_Row = Range("shadow_header_row").Row + 1
'Transfer_Number_Populate_Col = Range("Transfer_Number_Populate_Col").Column
Sheets("SHADOW1").Select
On Error Resume Next
Application.ScreenUpdating = False
'MsgBox ("Please Recheck before Populating, if you are sure then click YES"), vbYesNo, ""
'If Ans = vbYes Then
lngRow = Transfer_Header_Row
Do Until IsEmpty(Range("AC" & lngRow))
If Range("AC" & lngRow).Value > 1 Then
intInsertRows = Range("AC" & lngRow).Value - 1
Range("AC" & lngRow + 1 & ":AC" & lngRow + intInsertRows).EntireRow.Insert
Range("A" & lngRow & ":AC" & (lngRow + intInsertRows)).FillDown
Range("AD" & lngRow & ":AD" & (lngRow + intInsertRows)).Value = 1
lngRow = lngRow + intInsertRows
End If
lngRow = lngRow + 1
Loop
'End If
Call FORMULADATA
End Sub
The data on sheet [SHADOW1] is not yet duplicated. You can click on the button [ click to duplicate to see ] on the attached file.
My only problem I am having is for the required operation sequence number which is in column A because whenever in Column AC for example in row 4 the duplicate value [ the operator required] is 3. It duplicates it 2 times as 1 time is already existing and then the sequence in Column A shall become 2,3,4 instead of 2,2,2.
But also, it has to check in column B where If the poste is repeated it shall continue with the same number for example after the duplicate from row 4 It became 2,3,4,5,6,6,6,7 it’s because from cell B8 to B10 the poste is 4,4,4. Please see attached file on sheet [data after duplicate] for better understanding.
Right now, I am doing this manually. Can someone help how we can set the sequencing after the duplication is completed.
Sheet [data after duplicate] shows exactly what is needed after the duplication.
Thanks in advance for your support.
I am actually using this below code to duplicate rows based on the cell value. Which in this case is the column AC which represents number of operators.
Here is the code I am using which works perfectly to duplicate the rows based on the Column AC[ number of operators].
Code
Sub populate_operations_sequence()
Dim lngRow As Long
'start_Time = Time
Dim intInsertRows As Integer
Transfer_Header_Row = Range("shadow_header_row").Row + 1
'Transfer_Number_Populate_Col = Range("Transfer_Number_Populate_Col").Column
Sheets("SHADOW1").Select
On Error Resume Next
Application.ScreenUpdating = False
'MsgBox ("Please Recheck before Populating, if you are sure then click YES"), vbYesNo, ""
'If Ans = vbYes Then
lngRow = Transfer_Header_Row
Do Until IsEmpty(Range("AC" & lngRow))
If Range("AC" & lngRow).Value > 1 Then
intInsertRows = Range("AC" & lngRow).Value - 1
Range("AC" & lngRow + 1 & ":AC" & lngRow + intInsertRows).EntireRow.Insert
Range("A" & lngRow & ":AC" & (lngRow + intInsertRows)).FillDown
Range("AD" & lngRow & ":AD" & (lngRow + intInsertRows)).Value = 1
lngRow = lngRow + intInsertRows
End If
lngRow = lngRow + 1
Loop
'End If
Call FORMULADATA
End Sub
The data on sheet [SHADOW1] is not yet duplicated. You can click on the button [ click to duplicate to see ] on the attached file.
My only problem I am having is for the required operation sequence number which is in column A because whenever in Column AC for example in row 4 the duplicate value [ the operator required] is 3. It duplicates it 2 times as 1 time is already existing and then the sequence in Column A shall become 2,3,4 instead of 2,2,2.
But also, it has to check in column B where If the poste is repeated it shall continue with the same number for example after the duplicate from row 4 It became 2,3,4,5,6,6,6,7 it’s because from cell B8 to B10 the poste is 4,4,4. Please see attached file on sheet [data after duplicate] for better understanding.
Right now, I am doing this manually. Can someone help how we can set the sequencing after the duplication is completed.
Sheet [data after duplicate] shows exactly what is needed after the duplication.
Thanks in advance for your support.