copying data from one cell to another automatically

[[email protected]]

Hello, I have a large spreadsheet full of usernames, when the people call we need to log it on the second sheet of the workbook, this all has to be done automatically to save time. I have now got the button which copies data entered from one cell on the HOME page automatically onto the CallLog page however I now need to do a small button next to each username (these are in column A) which when pressed takes the username and puts it into the box which is cell G8 on the HOME page. The button needs to pull the information from the cell next to it (which is going to be column A) as every line in column B will need the button embeded into it next to each username. Thanks in advance for any help.

[bmmerkx]

Could you post an example workbook to clarify your issue?

[[email protected]]

Thanks, here is a basic example with the CallLog macro on it and fake usernames at the side, basically I need to have buttons in column B on every single line which can be pressed and auto populate the cell G8 with the username in column A.

[6StringJazzer]

Your "Assign To Call Log" button is assigned to a macro in another workbook ('\\WINSTON\SHARED\Orders 2010\CRM.xlsm'!assigning). It would be preferable to put the macro in this workbook.

I would not suggest creating a button for every username. This is very high-maintenance when there are changes to the list, which seems like it would be frequent.

I have attached an update to your workbook that allows you to double-click on any item in Column A to have it copied to G8. This same action could be used to put data on the other worksheet too, but I'm not quite sure what you want to happen there.

[[email protected]]

Wow that is great thank you so much, how do I copy that update onto the real thing?

[6StringJazzer]

Wow that is great thank you so much, how do I copy that update onto the real thing?

Open my version plus your own file in Excel.

You have to do this in the VBA development window. To get there from Excel, ALT-F11. On the left side is a folder list that shows each open Excel file plus the VBA components within it (each worksheet is treated as a VBA component here).

You will see both files listed to the left. Find my file, and double-click on Sheet1(HOME). On the right window you will see the code (simple isn't it? ). Hit CTRL-a to highlight it all, then CTRL-c to copy. Then double click on Sheet1(HOME) in your "real thing" file, and CTRL-v to paste it in the code window.

Then you're pretty much all set. Make sure you enable macros when opening the file.

Explanation for them that needs it:

This Sub is an event handler. It is a way for your VBA code to interact with Excel's runtime, and lets you intercept and handle events that would otherwise be handled by Excel.

Any time a user double-clicks on any cell in the worksheet (this code only works for the worksheet it is located in), Excel calls this Sub first. Target is the range where the user double clicked. The If checks to see if it occurred in Column 1. If so, it copies the value from the double-clicked cell to cell G8 (you can see how easy it is to change if you need to use a different cell). Setting Cancel to true tells Excel not to pursue the action it would normally take when a user double clicks (that happens to be opening the cell for editing, just like pressing F2).

If the user double clicks anywhere besides column 1, the default action occurs and the cell is opened for editing.

[[email protected]]

that is fantastic thank you so much