Copy and insert new row 5 rows up from bottom of sheet

Hello: I have very little experience with macros and VBA but have been playing around with them for the last couple of weeks. I have done lots of reading during this time both on this forum as well as numerous other websites. I have managed to glean many useful bits of information from this forum and for that I am thankful.

I have managed to create a spreadsheet that makes use of a userform to ensure people enter new data in the proper locations and in the proper format.

The one problem that I still have is that I would to copy a new row to the spreadsheet each time a new entry is made.

I am sure that there is a simple way to do this but I can’t seem to figure it out.

Here is what I have so far.

I did post this in the Excel Miscellaneous forum without any replies.
http://www.excelforum.com/showthread...highlight=xned

Any help would be appreciated.

Please Login or Register  to view this content.

I think that I am very close but the code I have right now only selects the cell in column A 5 rows up from the end, when what I need is to select the whole row.

Hi there,

I've slightly modified your code (see below) so it now does what you're asking in your post:

Please Login or Register  to view this content.

The significant difference here is that instead of storing the cell as an object (which includes everything; the cell address; cell value; background colour and a host of other parameters) in the LastRow parameter which is what you had, I've only stored the value that you're interested in which is the Row number. Because that's an integer I can store that parameter as an integer instead of an object too which makes the code a bit neater and, theoretically reduces the memory space taken (not that that's a big concern in this case).

The rest of the code has been changed so that it takes into account the fact that LastRow is now a number (=85 in the spreasheet you had saved) instead of a complete cell reference so:

Please Login or Register  to view this content.

which offsets the cell reference 6 cells upwards has changed to:

Please Login or Register  to view this content.

which picks the cell in column 1 on row 85 - 6 (79).


Hope that all maks sense. Does it produce the result you're after. Why do you need to insert a row?

Post back if you need any more help. I also learnt from scratch from a book and this forum has been invaluable. To get as far as you have in such a short time is v. impressive

Happy coding,

Tris

Tristan

It looks like it should work, I will give it a try when I get a chance.
Thanks for your help as well as the encouragement, it is much appreciated.

It might be a couple of days before I get a chance to check it out.

Just tried it out (couldn't wait) and it does exactly what I wanted.

I wanted to be able to add a new row each time an entry is made because I have labels along the bottom and this way they will always be next to the last entries.

Thanks again for your help.

Moderator: Please mark as solved. Thanks