Duplicate row and insert information from inputbox

[Prashant1211]

Hello Excel programmers, I have a excel file contain list of x number of products with n qty. to be packed in separate boxes. Some products can be packed in single box and some need to be multiple boxes because of high qty. Now i am trying to split the qty. so that i can pack it in different boxes. My requirement is when user run the macro then, below selected row a new row should be inserted with new box no. and qty.

After User select the row on selected row Macro should function like -

- Delete packing box no. of selected row and move the total qty in any empty cell
- an input box should appear to enter box no and qty. with tab "Submit"
- when user Submit the input first time. The information should go in selected row. The qty entered to be verified with Total qty (moved to empty cell in step 1)
- if the qty is less than again Input box should appear to enter new box no. and qty. This will be the second time user input the information so this time selected row to be copied and inserted below it. The information submitted second time should go in the newly inserted row. and this should continue unless full qty. is packed

I am in the learning stage and do not have much command on VBA and request for help.

below is my code. it duplicate the selected row in no. of time i enter. but this does not serve my full purpose. Can anyone help.


Sub Duplicaterow()

Dim xCount As Integer
LableNumber:
xCount = Application.InputBox("Number of Boxes", "Packing List", , , , , , 1)
If xCount < 1 Then
MsgBox "the entered number of box are not correct, please enter again"
GoTo LableNumber
End If



ActiveCell.EntireRow.Copy
Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(xCount, 0)).EntireRow.Insert Shift:=xlDown

Application.CutCopyMode = False
End Sub

 

[Prashant1211]

Can anyone help ?