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!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

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
1,439
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
1,439
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,305
Messages
5,836,534
Members
430,437
Latest member
Emilycr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top