problem with macro understaning "ctrl+down" to last entry in table

[Muzza86]

I have a column of names which gets its info from another sheet. e.g. every cell in this column says something like =a1 (from sheet1)

basically, when a new employee is added to the spreadsheet on sheet 1... I need to go to sheet two and drag the (=a1) down to a2 to show =a2 so the new name is now copied over to the table on sheet 2.

I KNOW how to do this, but the person who will be using it doesn't know anything about excel so im trying to create a macro to do it for them in the form of a button.

what I tried was... recording myself pressing on the top cell in the table, then pressing CTRL down to go to the last entry. then dragging that cell down.

It works fine for the first press of the button, but I assumed the second press would make the CTRL down command go to the cell I JUST created in the last press of the button but it doesn't - it doesn't do anything on the second press and just repeats same action.

why doesn't the ctrl down command in code go to the last LAST cell on the second press. >


I made a hash of explaining this but someone must get what I mean. to help ppl understand - this is the code:

Please Login or Register  to view this content.

[Olly]

Why not put something like this into the worksheet module for Sheet 1 - then if anything changes in Column A of your 'master' list, it will refresh the formulae in Sheet2:

Please Login or Register  to view this content.

[Muzza86]

why not? because I don't have a clue about private subs basically ha..

cheers, thought ill try and figure this out and apply it to mine to see if it works

[sktneer]

Or like this......

Please Login or Register  to view this content.

[Olly]

why not? because I don't have a clue about private subs basically ha..

cheers, thought ill try and figure this out and apply it to mine to see if it works

Right click Sheet 1 worksheet tab. Click View Code. Paste the code I posted above in the module which appears.

Return to Excel, and try adding a new value to the bottom of Column A, in Sheet 1. A corresponding formula will appear in Sheet 2.

[Muzza86]

ok let me simplify what im trying to do with exact example sheet.. see attached

[Olly]

Does that attachment actually reflect the worksheet structure you will end up working with?

Did you try following my instructions, to paste that code into the worksheet module? Did it work?

[Muzza86]

I need someone to tell me why my code doesn't work or to edit it. thanks,