How to run a macro based on cell double click in another sheet?

Status
Not open for further replies.

MidoOnly

New Member
Joined
Aug 12, 2014
Messages
3
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.
3- 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.
2- 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.
 
Last edited:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Moderator, code tags and the post are edited and reposted again. Kindly delete this thread. Thank You.
 
Upvote 0
Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: Forum Rules).
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top