macro messing up table

[daveyc18]

i have a table with three columns...whenever i use the macro to enter new data in the table, it messes it up and it's no longer dynamic. after the macro, if i manually enter data again, the table does not expand.

'put time stamp
Sheets("Time Stamp").Select


finalrow = Cells(Rows.Count, "A").End(xlUp).Row


'time
Range("c" & finalrow + 1).Select
ActiveCell.FormulaR1C1 = "=now()"
ActiveCell.Copy
ActiveCell.PasteSpecial xlValues


'date
ActiveCell.Offset(, -1).Select
ActiveCell.FormulaR1C1 = "=TODAY()"
ActiveCell.Copy
ActiveCell.PasteSpecial xlValues




Application.CutCopyMode = False

 

[NoSparks]

Nothing in that code indicates you are working with a true Excel table,
but if you are, try this

Dim oLo As ListObject
Dim oNewrow As ListRow

Set oLo = Sheets("Time Stamp").ListObjects(1)
Set oNewrow = oLo.ListRows.Add
With oNewrow.Range
    'date
    .Cells(1, 2).Value = Date
    'time
    .Cells(1, 3).Value = Time
End With

a couple of links for more insight on tables
https://www.jkp-ads.com/Articles/Excel2007TablesVBA.asp?AllComments=True
https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables

 

[daveyc18]

Nothing in that code indicates you are working with a true Excel table,
but if you are, try this

Dim oLo As ListObject
Dim oNewrow As ListRow

Set oLo = Sheets("Time Stamp").ListObjects(1)
Set oNewrow = oLo.ListRows.Add
With oNewrow.Range
    'date
    .Cells(1, 2).Value = Date
    'time
    .Cells(1, 3).Value = Time
End With

a couple of links for more insight on tables
https://www.jkp-ads.com/Articles/Excel2007TablesVBA.asp?AllComments=True
https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables

it's definuitely a table...i see when i check the name manager and when I manually type in something, a new row is autoamtically added.

to expand on what i said originally, when i run my ORIGINAL macro code, it looks like the new row is a new table (the filters show up in the new row and the filters still exist in the headers of the original table). when I run the code again, the table is not expanded.

 

[daveyc18]

it's definuitely a table...i see when i check the name manager and when I manually type in something, a new row is autoamtically added.

to expand on what i said originally, when i run my ORIGINAL macro code, it looks like the new row is a new table (the filters show up in the new row and the filters still exist in the headers of the original table). when I run the code again, the table is not expanded.

but your code worked!! thanks!

googled listobjects ...good to know!

now i know why when I was trying to reference a table for advance filtering purposes it wasnt working

 

[NoSparks]

but your code worked!! thanks!

how does it work?

what's this listobjects thing?

That's why the two links were included.
Those are sites of two members of this forum.