Copy & Paste Data Upon Click

Solved/Closed
wliang Posts 41 Registration date Thursday June 16, 2011 Status Member Last seen May 7, 2014 - Mar 4, 2012 at 11:24 PM
wliang Posts 41 Registration date Thursday June 16, 2011 Status Member Last seen May 7, 2014 - Apr 2, 2012 at 09:05 PM
Hello,
Good day.

I need some advices on how to copy and paste data upon mouse click.

I have a set of data on A2 to A10. When I hit cell B1 which contain the word "January", the data will be copied into B2 to B10. If I hit cell D1 which contain the word "March", then the data will be copied into D2 to D10.

In another word, I would like to have the data copied and pasted into the columns that I have clicked.

Appreciate the advice from everyone.

Thank you.

Best regards,
wliang
Related:

5 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Mar 5, 2012 at 09:34 AM
Hi Wliang,

I assumed you have one column for each month, so the code will only be activated when a cell in the range B1:M1 is selected.

This code should do the trick:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("B1:M1")) Is Nothing Then Exit Sub
Range("A2:A10").Copy Destination:=Cells(2, Target.Column)
End Sub

If you would like the code to work for the entire first row then change
Range("B1:M1") into Rows(1).

Best regards,
Trowa
0
wliang Posts 41 Registration date Thursday June 16, 2011 Status Member Last seen May 7, 2014
Mar 12, 2012 at 03:28 AM
Hi Trowa,

Thanks for your advice. The codes work just fine.

Can I apply these codes to a text box as well, i.e. when a textbox is clicked, the codes will be activated? Please advise.

Thanking you in advance for your help.

Best regards,
wliang
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Mar 13, 2012 at 11:38 AM
Hi Wliang,

You can activate the code by double clicking the textbox, but how does Excel know which column to paste in? Would an inputbox be something?

Try this:
Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
Range("A2:A10").Copy Destination:=Cells(2, InputBox("Please enter column letter:", "What is the destination column?"))
End Sub


Best regards,
Trowa
0
dengming Posts 2 Registration date Monday March 12, 2012 Status Member Last seen March 12, 2012
Mar 12, 2012 at 04:30 AM
If you would like the code to work for the entire first row then change
Range("B1:M1") into Rows(1).
Link deleted by a moderator
0
dengming Posts 2 Registration date Monday March 12, 2012 Status Member Last seen March 12, 2012
Mar 12, 2012 at 04:31 AM
Thanking you in advance for your help.

Link deleted by a moderator
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Mar 13, 2012 at 11:40 AM
Hi Dengming,

Is there a question you forgot to ask or did you just wanted to post your amazon link.

Best regards,
Trowa
0

Didn't find the answer you are looking for?

Ask a question
wliang Posts 41 Registration date Thursday June 16, 2011 Status Member Last seen May 7, 2014
Apr 2, 2012 at 09:05 PM
Hi Trowa,

Thanks for your advice. I tried the modified codes and they are working just fine.

Once again, thanks for your help.

Best regards,
wliang
0