Running a macro when double clicking a cell in another sheet

[MidoOnly]

Dears,
Good morning or good evening wherever you are.
I am new with VBA. So, I had to search online on how to do what I wanted to do with my workbook. But now I am stuck and I hope you could help me.




In sheet 1, there is a table of data.
Each row has a site code and some data related to this site.
I want to copy certain rows to sheet 4 based on a cell value containing the site code. So, I used the below code:

Sub CopyData()Dim myWord$
myWord = InputBox("Enter Site Code:", "Enter your word")
If myWord = "" Then Exit Sub




Application.ScreenUpdating = False
Dim xRow&, NextRow&, LastRow&
NextRow = 13
LastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For xRow = 1 To LastRow
If WorksheetFunction.CountIf(Rows(xRow), "*" & myWord & "*") > 0 Then
Rows(xRow).Copy Sheets("Sheet4").Rows(NextRow)
NextRow = NextRow + 1
End If
Next xRow
Application.ScreenUpdating = True




MsgBox "Site data is ready", 64, "Done"
End Sub

In Sheet 4, the user enters a site code in cell B8, the number of occurrences for that site is generated in cell C8 using a COUNTIF function.


What I need in my code is:
1- When a user double clicks cell C8 in sheet 4, the macro CopyData starts copying the data from sheet 1 based on the site code entered in cell B8 in sheet4 without the need for an InputBox. I need the rows pasted in sheet 4 starting from row 13.
2- If possible, when a row is copied from sheet 1, the first column of any row is not copied, which is column B as column A is already empty for the whole sheet.
3- The code automatically clears previously copied data available in sheet 4 before copying new rows each time a new site code is entered.


Please feel free to do modifications to my code or suggest a new one if my code is not good enough for the above mentioned tasks.




I hope I was clear describing my problem.




Thank you so much.

[westconn1]

1- When a user double clicks cell C8 in sheet 4,

you only want this for sheet4 in specific workbook?
if so try putting the code into the before double click event for sheet 4
for all sheets in workbook look at the workbook sheetbefore doubleclick event