Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0301· OpenAIAi2 WildChat-4.8M (AI2)

How should I write a VBA code that does the following. Search all sheets in the workbook…

  1. 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.

  2. 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 If
    

    Next ws

    End Sub

  3. User

    Thanks

  4. Assistant

    You're welcome! If you have any other VBA-related questions in the future, feel free to ask.