Insert Lines & sequential numbers based on quantity Closed

Question

I have an order form with multiple macros including a macro to "Total" each order to show only the items and the quantity of each item that is ordered at one time. I am in desperate need of a macro that can take ONLY the items being ordered, and generate another table below my order form and list serial numbers in a numerical sequence based on the quantity being ordered. This has become difficult for me to build since the visible rows, serial numbers, and quantity change between orders. Also, please keep in mind that the excel form is used as a template, that I copy the information from and paste into another program to actually place the order. With that being said, the number of columns in each table has to stay the same, blank columns need to stay visible, and the serial numbers have to go in the same column each time. Otherwise, I have to manually type out ALL of this information. 




After I apply my "Total" macro, I enter the beginning serial# for each item



Beginning on Row 70, my second table looks like this



I need a macro that will take the information from table 1 and list it in table 2 like this



Does this make sense? It is possible?

TrowaD · Answer

Hi Coley925,

Let me see if get this right.

Looking at your second pic, you want to copy the item in column B the number of times listed in column C to column H of pic 4. The serial number from column A (pic 2) will be placed in column G (pic 4) and increments by 1. The rest of the columns (pic 4) will be left alone.

Correct?

Best regards,
Trowa

TrowaD · Answer

Hi Coley,

See if the following code does what it needs to do:
Sub RunMe()
Dim x, qty, fRow, sn As Integer

x = 8

Do
    qty = Cells(x, "C").Value
    If qty > 0 Then
        
        fRow = Range("H" & Rows.Count).End(xlUp).Row
        Cells(x, "B").Copy Range(Cells(fRow + 1, "H"), Cells(fRow + qty, "H"))
        sn = Cells(x, "A")
        For Each cell In Range(Cells(fRow + 1, "G"), Cells(fRow + qty, "G"))
            cell.Value = sn
            sn = sn + 1
        Next cell
    End If
    x = x + 2
Loop Until x = 56

End Sub

Didn't have enough time to explain the code. But at least you can try it out. 

Maybe the code makes more sense to you then you thought, so let me know what is unclear to you and I'll be happy to clarify.

Best regards,
Trowa

Coley925 · Answer

So, I didn't have time to make a generic one so, I just removed the pricing and took some actual screen shots. The general setup stays the same between worksheets. the only thing that varies is where the asset/serial numbers begin. I need the macro to use the quantity in table1 columnC and generate a sequential list starting with the beginning number in table1 ColumnI and placing it in table2 Column G. I also need the corresponding description from table1 Column B to be placed in table2 ColumnI. I will be assigning the macro to the "Generate Asset Numbers" button. When this macro is ran, does a certain cell need to be highlighted, or will it run no matter what cell my cursor is in? Also, can you tell me what needs to be changed within the macro when my table2 begins on a different row between worksheets?

Table 1-


Table 2 - Rows 1-7 stay visible

TrowaD · Answer

Hi Coley,

Question 1:
"When this macro is ran, does a certain cell need to be highlighted, or will it run no matter what cell my cursor is in?"
Answer: The code will run no matter what cell or range is selected.

Question 2:
"Also, can you tell me what needs to be changed within the macro when my table2 begins on a different row between worksheets?"
Answer: Code line 10 will determine the first row in second table based on column H. The way this is done is by going to the last cell of column H (hit the End button and then Arrow down a couple of times until you get to the last row.) and hit End and Arrow up. The row your cursor is now on will be the as the header row of table 2. So if that works correctly, then  no adjustments are needed for different sheets with a different starting row of table 2.

Tip: Put the code in a module. Then on each sheet when applying the code to the button simply type this between the 2 given code lines:
Call RunMe_v2


Sub RunMe_v2()
Dim x, qty, fRow, sn As Integer 'Variables declared as numbers

x = 8 'Start at row 8

Do 'start Do loop
    qty = Cells(x, "C").Value 'place qty in memory
    If qty > 0 Then 'go to next row when qty is not >0
        
        fRow = Range("H" & Rows.Count).End(xlUp).Row 'determine the first row in second table based on column H
        Cells(x, "B").Copy Range(Cells(fRow + 1, "H"), Cells(fRow + qty, "H")) 'copy item description and paste below the first row in second table the number of times of qty
        sn = Cells(x, "I") 'place serial number in memory
        For Each cell In Range(Cells(fRow + 1, "G"), Cells(fRow + qty, "G")) 'start For Each loop for the same range as the item description
            cell.Value = sn 'place serial number in cell
            sn = sn + 1 'increment serial number by 1
        Next cell 'go to the next cell in the For Each loop
    End If 'code will continue here when qty is not >0
    x = x + 1 'increase row number by 1 for the Do loop
Loop Until Cells(x, "B").Value = vbNullString 'Do loop will stop when Excel finds a row without data in column B

End Sub

Let me know how this works out for you. 

Best regards,
Trowa

Coley925 · Answer

I added the macro, and it almost works! I played around with it a lot and figured out what the issue is. When the number is more than 5 digits it does not recognize it and gives me an error. I am not sure if this will help, but our  serial numbers are always 10 digits. Also, is it possible to have the macro paste the description as "special values" so that my formatting in table2 remains the same. 

You are AWESOME!!!

TrowaD · Answer

OK Coley, take a look at the adjusted code below. It will now work for bigger serial numbers and the description will be pasted as values only (make sure to remove the Bold and Italic format from table 2). Changes can be found on code line 4 and 15.

Sub RunMe_v2()
Dim x, qty, fRow As Integer 'Variables declared as numbers which reserves memory for numbers between -32,768 and 32,767
Dim sn As Long 'Variable declared as numbers which reserves memory for numbers between -2,147,483,648 and 2,147,483,647
x = 8 'Start at row 8

Do 'start Do loop
    qty = Cells(x, "C").Value 'place qty in memory
    If qty > 0 Then 'go to next row when qty is not >0
        
        fRow = Range("H" & Rows.Count).End(xlUp).Row 'determine the first row in second table based on column H
        Cells(x, "B").Copy 'copy item description
        Range(Cells(fRow + 1, "H"), Cells(fRow + qty, "H")).PasteSpecial Paste:=xlPasteValues 'paste below the first row in second table the number of times of qty, values only
        sn = Cells(x, "I") 'place serial number in memory
        For Each cell In Range(Cells(fRow + 1, "G"), Cells(fRow + qty, "G")) 'start For Each loop for the same range as the item description
            cell.Value = sn 'place serial number in cell
            sn = sn + 1 'increment serial number by 1
        Next cell 'go to the next cell in the For Each loop
    End If 'code will continue here when qty is not >0
    x = x + 1 'increase row number by 1 for the Do loop
Loop Until Cells(x, "B").Value = vbNullString 'Do loop will stop when Excel finds a row without data in column B

End Sub

Best regards,
Trowa

Insert Lines & sequential numbers based on quantity

6 responses

Similar discussions

Subscribe To Our Newsletter!