Need a macro to copy data and paste to bottom of a table

Question

I'm trying to make a sheet where I can input some data, press a button and that data gets added to the table below it.

I've tried recording a macro but it doesn't seem to work, so I'm thinking I need some VBA code. Every row of the table also has to have a unique ID number and the whole table needs to be sorted newest to oldest. For the ID number I'm currently using =ROW(S1), =ROW(S2) etc.

Answer 1

I'd use the following formula in S10:

=ROW(S10)-ROW($S$9)

This will remain valid even if you move the table up or down.

Assuming that your table is the first or only table on the sheet:

Sub AddRow()

    With ActiveSheet.ListObjects(1).ListRows.Add.Range

        .Offset(, 1).Resize(, .Columns.Count - 1).Value = Range("T3:X3").Value

    End With

End Sub

Answer 2

I deleted my previous reply. The VBA code in this post is an extensively edited version of my previous reply so please discard the earlier reply and refer to this one.

I believe that the formula provided by Hans V to create the unique Id's will result in the Id's being set relative to the row number and will not remain with the original records when the table is sorted as per your comment "the whole table needs to be sorted newest to oldest"

I would use the following formula in cell S3 (In the "Add New Data" field) and then it will increment each time a record is copied to the table. The records are inserted in the table as values (not formulas) and therefore the Id number will remain with its appropriate record when the table is sorted. (Edit Table1 in the formula to your table name and also ID if you should alter your column header.)

=MAX(Table1[ID])+1

The following code adds the new records to the table and then sorts Newest to Oldest on the date column. It then clears the contents from the "Add New Data" section. You will also see that there is a test line of code to ensure that the user has entered data in at least one cell in addition to the Id number before it will copy the data to the table. Because the new data is cleared after copying, the user should not be able to inadvertently copy the same record to the table multiple times.

You might like to think about using worksheet protection to prevent the formula in cell S3 being inadvertently over written by a user. If you want to do this then see the commented out lines of code for Unprotect and Protect. A table cannot resize on a protected sheet even if the entire table plus all rows below are unlocked and hence the Protect and Unprotect in the VBA code if you want to use protection.

 'Insert the following line at the top of a standard module before any subs.

Public Const strPassword = "ossie"      Edit "ossie" to your password

Sub AddRow()

    Dim ws As Worksheet

    Dim lstObj As ListObject

    Dim rngNew As Range

    Set ws = Worksheets("Sheet1")  'Edit "Sheet1" to your sheet name

    Set lstObj = ws.ListObjects("Table1") 'Edit "Table1" to you table name

    'Test if the Row to enter data has been populated with the Id plus at least one other field

    If WorksheetFunction.CountA(ws.Range("S3:X3")) < 2 Then

        MsgBox "Cell S3 plus at least one cell in range T3:X3 must be populated." & vbCrLf & _

                "Procesing terminated."

        Exit Sub

    End If

    'ws.Unprotect Password:=strPassword     'Optional if protection used for cell S3

    With lstObj.DataBodyRange

        Set rngNew = .Cells(.Rows.Count + 1, 1).Resize(1, .Columns.Count)

        rngNew.Value = ws.Range("S3:X3").Value

    End With

    With lstObj.Sort

        .SortFields.Clear

        .SortFields.Add Key:=lstObj.ListColumns("Date").DataBodyRange, _

                        Order:=xlDescending, _

                        DataOption:=xlSortNormal

        .Header = xlYes

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

    ws.Range("T3:X3").ClearContents    'Prevent saving multiple copies of same record

    'ws.Cells(3, "S").Locked = True     'Optional if protection used for cell S3

    'ws.Protect Password:=strPassword   'Optional if protection used for cell S3

 End Sub

Answer 3

Hi, Thanks for such a detailed response!

Not sure if I'm just being stupid but when I paste the code in I just see a lot of red and get Syntax Error.

Answer 4

Not sure if I'm just being stupid but when I paste the code in I just see a lot of red and get Syntax Error.

I am not aware of that occurring before so I am not sure exactly what is causing it. I just copied the code from the forum post into a blank workbook and it copies and pastes fine.

Did you copy the code from the forum post or from the email notification? If you copied from the email notification, then go to the forum post and copy from there.

If that does not work then I am wondering if there are some spurious characters already in the Module. Insert a new module into your workbook and then try copying and pasting the code into the new module and see what occurs.

The other thing that could occur is that for some reason there are spurious characters on the end of the line. Try positioning the cursor at the end of each of the lines and press the delete key and see if you can delete them. If you delete too many and the following line comes up to the end of the line you are on then just press Enter to move it back onto the line below.

I will be interested in how you go with it. It's my bed time now in my part of the world so I won't be back to it until morning.