Excel - Copy & Paste Data Upon Click

Ask a question


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)
End Sub

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

Excel - Find the number of occurrences of a number
Copy Data to Another Workbook in Excel