Paste details from one sheet to last line of table in another sheet

hoonose

New Member
Joined
Apr 20, 2021
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello

I've created a sales invoice with data validation search bar to filter my customer details from a table located on the adjacent protected sheet/tab.

What I would love to do, is to have an option to add a new customer to the last row of my table using a very simple copy and paste.

My sheet layout is Invoice / Data
I'd like new details to be entered on Invoice sheet in cells N3 to N8. After pasting to table I would like the text from N3 to N8 to be cleared.
Table starts on Data sheet from column C to column H

I've tried to mix and match codes from various solutions online, add new row and copy paste data but only ended up getting confused.

Would appreciate any assistance on this matter.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,906
Hello,

Perhaps:

VBA Code:
Sub makeso()

Dim sht As Worksheet
Dim sht2 As Worksheet
Dim LastRow As Long

Set sht = Sheets("Data")
Set sht2 = Sheets("Invoice")

LastRow = sht.Cells(sht.Rows.Count, "C").End(xlUp).Row

sht.Cells(LastRow + 1, 3) = sht2.Cells(3, 14)
sht.Cells(LastRow + 1, 4) = sht2.Cells(4, 14)
sht.Cells(LastRow + 1, 5) = sht2.Cells(5, 14)
sht.Cells(LastRow + 1, 6) = sht2.Cells(6, 14)
sht.Cells(LastRow + 1, 7) = sht2.Cells(7, 14)
sht.Cells(LastRow + 1, 8) = sht2.Cells(8, 14)

sht2.Range("N3:N8").ClearContents

End Sub
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,133,271
Messages
5,657,765
Members
418,411
Latest member
Excellency

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