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.
Another link for my question: http://www.mrexcel.com/forum/e…er-sheet.html#post3902946