Forum Discussion
MS Excel VBA Code .
Hi Everyone!
I make a excel sheet for store stock maintain and there I put the data which I want to enter the right side table and there it's look like a slip table, now I need to put the next entry data to the below row of last entry, it means first entry will be placed in first row of table then the second entry will be second row and running,,, and there I also want to give the entries a auto serial number that means when I entry first data to the table then it will show serial no 1 to the serial column then second entry will show serial no 2 to the serial column and running,,
I tried to make it but facing some problems. I attach below some screen short for understanding,, Please any of you have solution then please reply...
In H7, enter the formula
=ROW(H7)-ROW(H$6)
This should propagate automatically to all existing and new rows of the table.
Macro:
Sub Add_To_Slip() Dim tb As ListObject Dim lr As ListRow Set tb = Range("H6").ListObject Set lr = tb.ListRows.Add Range("E6:E12").Copy lr.Range(1, 2).PasteSpecial _ Paste:=xlPasteValuesAndNumberFormats, _ Transpose:=True Application.CutCopyMode = False Range("E7").Select End Sub
In H7, enter the formula
=ROW(H7)-ROW(H$6)
This should propagate automatically to all existing and new rows of the table.
Macro:
Sub Add_To_Slip() Dim tb As ListObject Dim lr As ListRow Set tb = Range("H6").ListObject Set lr = tb.ListRows.Add Range("E6:E12").Copy lr.Range(1, 2).PasteSpecial _ Paste:=xlPasteValuesAndNumberFormats, _ Transpose:=True Application.CutCopyMode = False Range("E7").Select End Sub- Brother it's work. but now I have one thing to do that is the serial number how can i get the serial number auto after a data entry,,,
See my previous reply. If you enter the formula that I proposed in H7, it should automatically be copied to new rows as you add them.
