Using VBA with Tables

Question

I am trying to create a simple insert row to 'table', 'copy' second row and 'paste' into first row. Currently I have just created the below as it was logical to me and I cannot find any reference on the internet so far.The table has another table above it so the cell are constantly shuffling down and have no point of reference aside from the table itself.

It inserts a row but does not copy and paste the values, Formulas, and formatting (data validated droplist) from second row. I also want to clear the contents of the first and fifth column in the first row. there are no error messages.

Sub AddPkgBeer()
'

' AddPkgBeer Macro
'

'
Sheets("BEER MENU").Select

ActiveSheet.Unprotect Password:="password"

Range("Table36356").Select

Selection.ListObject.ListRows.Add (1)

ActiveSheet.ListObjects("Table36356").DataBodyRange(2).Select

Selection.Copy

ActiveSheet.ListObjects("Table36356").DataBodyRange(1).Select

Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

ActiveSheet.ListObjects("Table36356").DataBodyRange(1, 1).Select

Sheets("BEER MENU").Select

ActiveSheet.Protect Password:="password"

End Sub

You may want to read [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) as a starting point. — BigBen, Feb 16 '21 at 14:02

score 1 · Answer 1 · answered Feb 16 '21 at 14:04

Sub AddPkgBeer()

    Dim ws As Worksheet, rng As Range, tbl As ListObject
    
    Set ws = ActiveSheet
    Set tbl = ws.Range("Table36356").ListObject
        
    tbl.ListRows.Add 1
    Set rng = tbl.ListRows(1).Range
    tbl.ListRows(2).Range.Copy rng
    rng.Cells(1, 1) = ""
    rng.Cells(1, 5) = ""
    
End Sub

Using VBA with Tables

1 Answers1