copy row in vba - add the same autonumber to few rows

Question

I try to copy in the excel few rows to a table, and give the same auto number to the rowa I add in each opparation. I have a macro that copy the rows and gives the first line (of the new lines I just added) the next auto number. I want to add the same number to the other rows. (and each time there can be different numbers of rows, but not more then 16). my macro is:

Sub copy_order()
'

'
    Sheets("orders").Select
    Application.Goto Reference:="product"
    ActiveCell.Range("A1:D16").Select
    Selection.Copy
    Application.Goto Reference:="orders_table"
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 1).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, -1).Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=R[-1]C+1"
    ActiveCell.Offset(1, 0).Range("A1").Select
        Application.Goto Reference:="product"
    ActiveCell.Offset(0, 0).Range("A1:C1").Select
    Selection.ClearContents
    Application.Goto Reference:="orders_table"


End Sub

thank you, Keren.

[INTERESTING READ](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Siddharth Rout, Dec 13 '13 at 17:56
sure, it's not exactly input, I have all the data in excel. I have exist table: autonum(it's order_number); product; amount ... and I want to add 2 lines: first: product-pen;amount-3. secound: notebook;4.... so I want those to lines to have the same ordernum. hope it's clear.... thank you! — user3100355, Dec 13 '13 at 18:21

score 0 · Answer 1 · answered Dec 13 '13 at 18:35

0

Not sure I followed all your offsets correctly, but this should get you close...

Sub copy_order()

    Dim rngDest As Range, rngCopy As Range, sht As Worksheet, num
    Dim c As Range

    Set sht = Sheets("orders")

    Set rngCopy = sht.Range("product").Range("A1:D16")
    Set rngDest = sht.Range("orders_table").Cells(1).End(xlDown).Offset(1, 0)

    rngDest.Resize(rngCopy.Rows.Count, rngCopy.Columns.Count).Value = rngCopy.Value
    num = rngDest.Offset(-1, -1).Value + 1

    Do While Application.CountA(rngDest.Resize(1, rngCopy.Columns.Count)) > 0
        rngDest.Offset(0, -1).Value = num
        Set rngDest = rngDest.Offset(1, 0)
    Loop

End Sub

answered Dec 13 '13 at 18:35

Tim Williams

154,628
8
97
125

thank you so much, it doesn't work like this. maybe you can expline me what you added so I could make it fitted to my sub? – user3100355 Dec 13 '13 at 18:54
If I could do that it would have worked off the bat ;-) Can you update your question with 1)Adresses for your two named ranges `products` and `orders_table` 2)some description of what goes where, particularly where the "autonumber" should go. – Tim Williams Dec 13 '13 at 20:35

copy row in vba - add the same autonumber to few rows

1 Answers1