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 555
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
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
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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
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
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
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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
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