How should I write a VBA code that does the following. Search all sheets in the workbook…
- User
How should I write a VBA code that does the following. Search all sheets in the workbook that have names that are a three letter word. In column B of each sheet named with a three letter word, find each occurrence of the word “Service”. Some sheets have more than one occurrence of the word “Service” in their column B. For each occurrence of the word “Service” take the values of column H and Column J of the same row and insert each match into seperate rows of column A, column B and column C in Sheet ‘Test 3’ in the following order, column A name of the sheet where the match was found, column B the value found in column H of the same sheet and the same row where the match was found, and in column C the value found in column J of the same sheet and the same row where the match was found.
- Assistant
Sub SearchAndCopy()
Dim ws As Worksheet, wsDest As Worksheet Dim lastRow As Long, destLastRow As Long Dim i As Integer, j As Integer Dim searchString As String
Set wsDest = Worksheets("Test 3")
For Each ws In Worksheets If Len(ws.Name) = 3 Then lastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To lastRow searchString = ws.Cells(i, "B").Value If searchString = "Service" Then destLastRow = wsDest.Cells(Rows.Count, "A").End(xlUp).Row + 1 wsDest.Cells(destLastRow, "A").Value = ws.Name wsDest.Cells(destLastRow, "B").Value = ws.Cells(i, "H").Value wsDest.Cells(destLastRow, "C").Value = ws.Cells(i, "J").Value End If Next i End IfNext ws
End Sub
- User
Thanks
- Assistant
You're welcome! If you have any other VBA-related questions in the future, feel free to ask.