MS Excel VBA Code .

[KAM_Mumin]

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...

[#2 · Accepted solution]

@KAM_Mumin 

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

[#3]

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,,,

[#4]

@KAM_Mumin 

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.

[#5]

@HansVogelaar  I use some little code after your given code for making auto serial number,, and there is a reason for that because I need to delete all the rows of the table so if I put formula in the table cell then it will also clear but if I use this vba code then it will stay,,, thanks brother for helping me,, I hope you will be by my side afterdays solutions,,,

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
    
Sheets("Home Page").Select
    For m = 7 To Cells(Rows.Count, "I").End(xlUp).Row
    If Cells(m, "I").Value <> "" Then
        Cells(m, "H").Value = m - 6
    End If
        Next m
        
    Range("E7").Select