Copy Different Cell Values Using VBA Code

Hi Experts,

I've 2 separate worksheets: Sheet1 and Sheet2 as attached (filename: Sampe1.xls).

Data entries are done mainly in Sheet2 with all the required fields, but there are only 3 values that I'm interested at.

Those are: Request No, Requestor Name and Student Name.

These 3 values in Sheet2 are to be transferred automatically to Sheet1 by a click of button.

All input data in Sheet2 will be wiped out when it's already copied to Sheet1 with a click of button.

All input data in Sheet2 will be copied to Sheet1 to the selected row (selected by user), so the selection is flexible.


SAMPLE DATA:
==========
Sheet2
--------
Request No: 1003
Requestor Name: Elizabeth
Student Name: Student 1, Student 2, Student 3, Student 4

Sheet1
--------
1003 Elizabeth Student 1, Student 2, Student 3, Student 4


Can this be done this way?

Please help.

Thank you.


- Jack

Hi nilem,

Thanks for your reply and help.

I've created a button in Sheet2 with your coding.

Had tried it, but the output is different, see below.

-> Values needed are from Sheet2 in cells: C5, C7, C9
-> When I clicked the button, it copied these values to Sheet2 in different cells: B14, C14, D14.
-> These values from Sheet2 should be copied to Sheet1 in cells: B6, C6, D6

-> Also, how to clear Sheet2 cells: C3, C11?

I've attached the updated file.

Please help.

Thanks.

Hi nilem,

Thanks for your reply and help.

It's much better now

But, possible to allow a more flexible way to copy those values to Sheet1?

For example:
-> I want to copy these values from Sheet2 to Sheet1 in any empty cells within "Column B" only.
-> Now cell B4 to B8 in Sheet1 already filled with some data, I want to copy the new values to B10 or B13 or other empty cell, depending on where I point my mouse pointer.
-> If I point it to anywhere else outside "Column B", there will be a warning message as an indication.

See attached file, Sampe3.xls

Can it be done this way?

Thanks for your help.

vishal9002 ,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Hi vishal9002,

Appreciate if you don't hijack my post with your own questions.

Thank you.

Hi nilem,

Are you able to help?

Thank you.

Hi nilem,

Appreciate for your help.

It's really great to see what those coding can do Thanks

I've performed some testing on some cases and hit with 2 errors, see attached images.

ERROR1 (image)
-> On Sheet2, pressed the button, did not provide any cell values, clicked OK. Result: error message appear.

ERROR2 (image)
-> On Sheet2, pressed the button, went to Sheet1, did not provide any cell values, clicked OK. Result: error message appear.

Will it be possible to provide some more friendlier messages than those error messages?

Thanks.

Hi nilem,

Once again, appreciate for your kind help.

It works almost perfect as expected

I got 1 minor request, could you help to change below message?

From:
You have chosen [Sample5.xls]Sheet1!$B$11. Are you sure?

To:
You have chosen Sheet1 Cell B11. Are you sure?

See attached image.

Thank you.

Hi nilem,

Once again, appreciate for your kind help.

I just spotted 1 issue, if I select 2 cells (B9, B10) together in Sheet1, the values will be copied to these cells together in row 9 and 10, see attached image.

If I select either cell B9 or B10, it will copy to either cells only, not both cells together.

Possible to restrict and prevent it?

Thank you.

Hi nilem,

Many thanks and appreciate for your help and patience.

All are working as expected now.

I'll close this thread.

Have a nice day

Hi nilem,

Just wondering if you could help out on below items:

1) On sheet2, I've data on merge cells, can this data be copied out to the respective sheets?

2) How to copy the data to differente worksheets, for example: Sheet3 and Sheet4?

3) How to clear content using another button?

Please refers to the attached file for sample data (Sample4.xls).

Appreciate for your help.

Thank you.

Hi nilem,

Thanks for your help.

Please see my comments below.

Please Login or Register  to view this content.

Could you help to modify the above coding to cater for below changes?

1) On sheet2, I've data on merged cells, can this data be copied out to Sheet1, Sheet3 and Sheet4?

For example:
---------------
Sheet2
-> Raw5 -> Cell C5, D5, E5 are merged cell (Yellow highlighted)
-> Raw7 -> Cell C7, D7, E7 are merged cell (Yellow highlighted)
-> Raw9 -> Cell C9, D9, E9 are merged cell (Yellow highlighted)

See sample data as attached (Sampe5.xls)

2) How to clear content using another button?

Thank you for your help.

Hi nilem,

Sorry for the confustion.

I've managed to modify the codes to cater for the changes.

However, I'm a bit stuck with below code. How to use the same condition to cater for different worksheet?
For example: Sheet3 and Sheet4?

Please Login or Register  to view this content.

Thank you.

Hi nilem,

How to clear the content for an individul cell?

For example: clear content for cell C5, C7, C9

I've tried below codes, but it's not working.

Please Login or Register  to view this content.

Appreciate if you could help.

Thanks.

maybe so

Please Login or Register  to view this content.

about ClearContents
if you have merged cells (eg C5:E5), then so

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Hi nilem,

Ok. Will try it out.

Thanks for your help.