How do I insert a row and then paste data into it?

[Betsvigi9]

I have a simple internal requisition form which I want the users to be able to add extra lines to if necessary. As they input items to the requisition form it totals up at the top to tell them how much they have spent.

When I insert a row, I want to be able to paste a formula into one of the cells in that row by copying it from the cell above it. How do I reference the new row once I have inserted it and how do I reference the row above it.

I'm sorry if this question has been answered before, but if it has I cannot find it. I've done a lot of coding in access, but virtually none in excel so please be gentle with me!

Thanks.

 

[DaleWatson123321]

Hi,

This will likely be what you want. Change the columns reference (currently 3) to whatever column your formula is in.

Sub Insert_Row()
ActiveCell.EntireRow.Insert
r = ActiveCell.Row
Cells(r - 1, 3).Copy Destination:=Cells(r, 3)
End Sub

Hope this helps. Please advise as to how you make out.

Regards, Dale Watson

 

[Betsvigi9]

Thanks for your help. Whilst the code you posted didn't work exactly, I had a problem with the copying part, it definitely pointed me in the right direction. I used activecell.offset to move up and down and copied and pasted. I know that's probably not the best way of doing it, but it works and at the moment that is the main thing.

Once again thanks.