Copy data from one sheet (Fixed Cells and sheet) to another sheet

i have created a multi sheet work book that i am going to use for NFL fantasy draft next year. I want to be able to copy a name from one sheet (Available Players), paste it to a cell in another sheet (Round 1 through Round 20). The cell that will be copied is fixed but the place where it will be pasted will be different and may be on a different sheet. also i would like to change the color of the copied cell to "greyed" out or cut if it can not be greyed out. I have created a button and put in a macro that i created but have been having problems with it, generic 1004 errors that i can not figure out. i am attaching the document. any help is greatly appreciated.

if this post is incorrect in anyway, please let me know. thank you.

Here's how I would do it. Look over in "Available Players" column U, I've created a self-diminishing list of players. All the ROUND1-20 sheets have a dropdown list in the selection range G9:G20. As a name is selected ANYWHERE on those sheets, the name is removed from the next drop box. There's no way to pick a name twice.

As you requested, your original list in column D also greys out the used names so you can scan the list easily, but DO NOT CUT AND PASTE. Just use the drop box in each cell on the Round1-20 pages to make each selection.

Ladainian Tomlinson is already grey because he's chosen on the Round1 page.

NOTE: There is a named range in column U that is being used for the validation lists in the drop down boxes.

NOTE: For the KEY formula in column S to work, I had to remove the spaces in the names of your ROUND1-20 sheetnames.

By the way, 20 sheets seems a lot when your DRAFT RESULTS sheet could do ALL the work of all 20 sheets.

Well, if it's all about the show....hehe. I took out all the stuff I added and gave you a simple copy macro like you originally envisioned.

HOWEVER, the copying is being done into the DRAFT RESULTS page, and ROUND1-20 has the formulas that reflect the values from the DRAFT RESULTS.

In short, don't edit anything on the ROUND1-20 sheets, it's dynamic. All the work is being done on Available Players and Draft Results.

Your command button does the work based on the HIGHLIGHTED cell, so I made it so the button is always visible.

Please Login or Register  to view this content.

No, you didn't read my message about how it's working. when you click DRAFT PLAYER it's not putting ANYTHING in the round sheet, it's putting an entry in the DRAFT RESULTS page. The individual round sheets are all getting their entries from the DRAFT RESULTS page.

Anyway, here you go, another button has been added to reset the sheet FOR you so it's done correctly. Don't add/delete/copy anything.

In fact, I realized you could draft empty cells and the same player multiple times, so I added in code to keep that from happening. Try it..try the same player multiple times or drafting on an empty cell.

Also added code to jump you to the ROUND you're currently in...good for the "show" right? Remember, don't edit anything on those pages, they're live links to the draft results.

EDITED: see next post

Sorry, I found multiple problems with the sheetnames and the named boxes at the top of the round sheets, so I inserted a formula that will simply ALWAYS display the proper sheetname. Also, I realized an "are you sure" on the DRAFT RESET button was probably a good idea, so that's in there now, too.

Hopefully this does it...

Please Login or Register  to view this content.