VBA and pasting into a table

Rush01

New Member
Joined
Mar 11, 2019
Messages
3
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!
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Rush01

New Member
Joined
Mar 11, 2019
Messages
3
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

Well-known Member
Joined
Mar 15, 2013
Messages
997
Office Version
  1. 2010
Platform
  1. Windows
try this
Code:
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

Well-known Member
Joined
Mar 15, 2013
Messages
997
Office Version
  1. 2010
Platform
  1. Windows
Oops. Bed is a range you've already set, not a named range, so it's
Bed.Copy
 

Watch MrExcel Video

Forum statistics

Threads
1,109,368
Messages
5,528,280
Members
409,814
Latest member
Leon_Al

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top