Running a macro when double clicking a cell in another sheet
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:
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.
