Automatic Replication

**Dr Stupid** · 02-06-2008, 07:43 AM

Hi once again.

This time I have a sheet that holds some simple details, but for the less gifted PC users, I need a command button to perform a replication function, but unfortunatley, I'm not that skilled with programming.
There's a screenshot attached, but one of the cells has a Vlookup function in it.
I need the Vlookup to be replicated as well (onto a new row when the button is pressed). Also, when the row is replicated, it would be useful if all of the cell contents were empty to let new records be put in.

One last thing...I've made these style of buttons before (New Record). But whenever I actually press it with the code in, the button itself moves to a random position on the page... I have no idea why, but it isn't exactly useful.
Any ideas on how to stop it?

Please help!....again...

Cheers.

**royUK** · 02-06-2008, 07:51 AM

Do you want to copy all the data as well as formulas?

There is a way in Excel options to automate Excel to copy formulas & formats in Tables. From the Tools Menu select Options, then Edit & make sure that "Extend list formats & formulas" is checked.

**Dr Stupid** · 02-06-2008, 08:02 AM

Nah, I just need the formulas. They ought to bring back "N/A" if theres no data to support it, but the necessary data is put in manually.

**royUK** · 02-06-2008, 09:03 AM

Have you tried the manual way from Options?

Try this VBA

Option Explicit

Please Login or Register  to view this content.

**Dr Stupid** · 02-06-2008, 10:19 AM

Thanks again. It works, but...

Please Login or Register  to view this content.

This does delete the data from the new row, only problem is that it also changes the value of my vlookup on the same row to "#Ref!"
As a result, I removed that line and the copying works fine, but I'm at a loass to why it changes the value of my vlookup. I debugged and it turns out the part that changes is:
=VLOOKUP(LEFT($E6,4),Kits!$A$1:$B$2,2,FALSE)
The bit in bold becomes "#Ref" when the code runs.

Is there any chance you could tell me how to delete individual cell contents whilst the code runs so I can pick them off one by one?

Cheers!