How can I get a macro to run by clicking in a cell.

I have numerous columns of data. When I click in the column title in row 4,
I then want the data in rows 6 to 314 in that same column to be copied to
column D. (From there it feeds through to a report sheet). I have created
the VB for the copying and it runs fine whenever a cell in row 4 is
selected. But I cannot get it to run on its own simply by selecting the
cell.

How is this done please?

Thank you in advance

Peter

In the Sheet code module for the appropriate sheet, use the
SelectionChange event procedure. This will execute whenever you
select a cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 4 Then
' your code here
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Peter" wrote in message
...
How can I get a macro to run by clicking in a cell.

I have numerous columns of data. When I click in the column
title in row 4,
I then want the data in rows 6 to 314 in that same column to be
copied to
column D. (From there it feeds through to a report sheet). I
have created
the VB for the copying and it runs fine whenever a cell in row
4 is
selected. But I cannot get it to run on its own simply by
selecting the
cell.

How is this done please?

Thank you in advance

Peter

Suppose you have MyMacro typed in Range("C1"). To run MyMacro by clicking in
the cell C1, place this code in the ThisWorkbook module:


Public Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)

Dim sMacro As String

If Not Application.Intersect(Range("C1"), ActiveCell) Is Nothing Then
If Application.ScreenUpdating = True Then
sMacro = Range(Target.Address).Value
Application.Run sMacro
End If
End If

End Sub




Peter wrote:
How can I get a macro to run by clicking in a cell.

I have numerous columns of data. When I click in the column title in row 4,
I then want the data in rows 6 to 314 in that same column to be copied to
column D. (From there it feeds through to a report sheet). I have created
the VB for the copying and it runs fine whenever a cell in row 4 is
selected. But I cannot get it to run on its own simply by selecting the
cell.

How is this done please?

Thank you in advance

Peter