VBA and pasting into a table

[Rush01]

Hi all

I've done my utmost to find solutions elsewhere but despite my best efforts I'm still lost.
I'm trying to set up a bed board for a hospital ward, I've managed the basics, setting up a table to record who is in what bed, but I need now to be able to archive the patients when they leave in a separate table for lookup later. I'm using buttons for each "bed" to set the range (Bed) before calling the below Sub and then clearing the row.
I'm coming unstuck when pasting, as I want to paste the set range into the last row of Table1, but each time I paste it goes beneath the table instead! The code I have at present is -



Sub CopyFormulas()
Dim sht2 As Worksheet


Set sht2 = Sheets("Sheet2")


Bed.Copy
sht2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False


response = MsgBox("patient has left the ward")


End Sub

I've tried various ListObjects solutions, but to be honest I'm not sure I understand them!

 

[NoSparks]

Add a new row to the table and paste into it.
See https://www.jkp-ads.com/Articles/Excel2007TablesVBA.asp?AllComments=True
especially the Inserting rows and columns part and oNewRow.

 

[Rush01]

Hi NoSparks

I did review this page before, but couldn't get my head round how to get it to fit with what I need. I think I'm missing a value for the "Set oNewRow" line. I've altered the Selection & ListObjects to reflect the table. But am getting "Object Required" when running the code, and the oNewRow= nothing in debug.
I'm 100% sure I'm missing something really basic, but can't for the life of me figure what.

 

[NoSparks]

try this

Dim sht2 As Worksheet
Dim oLo As ListObject
Dim oNewRow As ListRow

Set sht2 = Sheets("Sheet2")
Set oLo = sht2.ListObjects(1)   'the first table on this sheet
Set oNewRow = oLo.ListRows.Add  'adds new row to bottom of table

Range("Bed").Copy
oNewRow.Range.PasteSpecial

 

[NoSparks]

Oops. Bed is a range you've already set, not a named range, so it's
Bed.Copy

 

[Rush01]

Fantastic, that did the trick. And it makes sense too!
Thanks