Copy and paste row with dropdown, before cells and then reset everything

[DcoltTech]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Product code[/TD]
[TD]Product Description[/TD]
[TD]Quantity[/TD]
[TD]price each[/TD]
[TD]total price[/TD]
[/TR]
[TR]
[TD]a.product
dropdown[/TD]
[TD]a.code[/TD]
[TD]a.description[/TD]
[TD]1[/TD]
[TD]£3.99[/TD]
[TD]£3.99[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]vat[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]total[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hi there,

I am looking to create a script, will copy and paste the first row of products, then paste that into the next blank row above vat. When that is pasted, I would like it to reset all cells to their original syntax, and reset the dropdown. I will then use "ActiveCell.Offset(1).EntireRow.Insert" to insert another blank row under this. I currently have the copy and paste script.

Sub Copyandpaste()
'
' Copyandpaste Macro
Range("$A$18:$F$18").Select
Selection.Copy
Range("A49").Select
ActiveSheet.Paste

End Sub

How would I do this?

Thanks in advance.

 

[AlphaFrog]

What original syntax? What dropdown?

Record a macro where you do everything manually and then post the code here. It then could be modified.

TIP: Because the Vat cell will move each time you insert a row, it would be helpful to create a Named Range for the Vat cell. That cell could then be referenced in the code by its name, and the code would not have to go "find" it. The named range will automatically change with the Vat cell location each time a row is inserted above it.

 

[DcoltTech]

Hi there, and thanks fr your reply.

I have linked the basic spreadsheet : http://1drv.ms/1vu6VPF
The style I want: http://1drv.ms/1vu7l8p

Basically, I wan to get rid of the block of select products rows. I just want one, and under that a blank row, and then a box where the price and vat etc goes, as seen in the second link. I would then like a macro, that when run, copies the row, adds a row under it then pastes it. I would then like it to reset the dropdown, and show the original syntax.

Regards

DcoltTech