Finding Text and Copying Row to Another Sheet

Copper Contributor

Hi there, I am attempting to use VBA to:

  1. Find Text String "CLOCKIFY" or "DROPBOX" in the source sheet called "Expenses-2022" tab/sheet under the "D" column and
  2. Copy the rows that contain either one of those strings
  3. Paste each into a row in the "Web-Vendors" tab/sheet in the same Excel doc.

When I click "Run" it does not do anything. No error code.

Here is my attempt using CLOCKIFY only (I don't know how to add both CLOCKIFY AND DROPBOX):
Sub CopyRowsToWebVendorsTab()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow As Long
Dim i As Long

' Define source and target worksheets
Set sourceSheet = ThisWorkbook.Sheets("Expenses-2022")

' Check if the target worksheet "Web-Vendors" exists, create it if not
On Error Resume Next
Set targetSheet = ThisWorkbook.Sheets("Web-Vendors")
On Error GoTo 0
If targetSheet Is Nothing Then
Set targetSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
targetSheet.Name = "Web-Vendors"
End If

' Find the last row in the source worksheet
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "D").End(xlUp).Row

' Loop through each row in the source sheet
For i = 1 To lastRow
' Check if the cell in column A of the current row contains "CLOCKIFY"
If InStr(1, sourceSheet.Cells(i, 1).Value, "CLOCKIFY", vbTextCompare) > 0 Then
' Copy the entire row to the target sheet
sourceSheet.Rows(i).Copy targetSheet.Cells(targetSheet.Cells(targetSheet.Rows.Count, "D").End(xlUp).Row + 1, 1)
End If
Next i

' Clean up
Set sourceSheet = Nothing
Set targetSheet = Nothing
End Sub

2 Replies


If InStr(1, sourceSheet.Cells(i, 4).Value, "CLOCKIFY", vbTextCompare) > 0 Then


If you want to search in column D you can change the above line of code to 4 for column D. The change is highlighted in red.

@OliverScheurich  Thank you! I will give that a try.