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.
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)
If you would like the code to work for the entire first row then change:
Range("B1:M1") into Rows(1).
This document, titled « Excel - Copy & Paste Data Upon Click », is available under the Creative Commons
license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM