Excel - Copy & Paste Data Upon Click

February 2017




Issue


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.

Solution


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). 

Related


Published by aakai1056. Latest update on April 12, 2012 at 07:26 AM by aakai1056.
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 (ccm.net).