copy datal to another worksheet once an option is selected

[feliciawong]

Hi,

Please help me with the following action:

On Worksheet A, when "Accept" is selected from the option list of field "Offer", data from the field "Student ID" of the same row in the same Worksheet will be copied to a NEW line on Worksheet B under the field "Student Number".

Thanks for your help.

[arlu1201]

What are the column numbers for Offer and Student ID?

[feliciawong]

Hi, thanks for helping. The column for "Offer" is AD column for Student ID is C on Worksheet A; and the column for Student Number is A on Worksheet B.

Await your advice,

[arlu1201]

Do you want this to be instant? As soon as you select the value in the dropdown, the row should be copied? But what if the user selects the dropdown twice? The copy will happen twice. Why not have a button in this case? Select all the dropdown values and then click the button for the copy to happen.

[feliciawong]

This is an excellent idea. I agree to add a button to copy. By the way, it does not copy the entire row, it just copies data in the same row under column C.

Thanks for your help. Again await your prompt advice,

[arlu1201]

Try this code

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

[feliciawong]

Hi,

I have tried and it worked. Thanks a million - you saved my life.

There is only one more requirement - can the Student ID from Worksheet A be copied to the bottom of the list in Column A of Worksheet B instead of inserting in Column A by numeric order?

Also, I will try to add a button which incorporates your macro instead of asking the users to follow the steps.

Thanks and await your advice,

[feliciawong]

Hi,

I got problems with your Macro. It worked perfect until an hour ago. Instead of copying the data under Column A in Worksheet A, it copied data under Column C. Please help .

Thanks and await your prompt response.

[arlu1201]

You said in post 3 that

The column for "Offer" is AD column for Student ID is C on Worksheet A; and the column for Student Number is A on Worksheet B.

So thats why it copies data from C of worksheet A to A of worksheet B.

It does not copy it in numerical order, unless you have a sort code elsewhere in your file. It copies it to the next available row.

[feliciawong]

Hi,

Thanks for your prompt reply. I have changed from column C to column A in your script. But it still did not work. I have attached screenshots for your information.

Thanks

[arlu1201]

When you say it does not work, are you getting any error?

[feliciawong]

I did not get any error messages. As per my screenshots sent, instead of copying the correct data (which is the student ID), wrong data (country) was copied.

[arlu1201]

Ok, so once you changed it to A from C, did it work properly?