VBA & Macro Problems

Excel VBA: Copy Rows Containing CLOCKIFY or DROPBOX to Another Sheet

Steve K Nov 12, 2026 868 views

Question details

The user needs a VBA macro that finds rows containing either CLOCKIFY or DROPBOX in column D and copies those rows to another worksheet.

Product: Microsoft Excel VBA
Device & OS: not provided
Scenario: A worksheet contains searchable text in column D, and matching rows must be transferred to a destination sheet.
Observed behavior: The existing macro searches the wrong column, does not correctly apply an OR condition for both keywords, or calculates the next destination row unreliably.

Before you start

Confirm the source and destination worksheet names used by the macro, and verify that the search text is stored in column D. Check whether column A on the destination sheet contains existing data because it determines the next available row.

Solution 1Recommended

Update the VBA macro to search column D for either keyword

Use this solution when the macro must match CLOCKIFY or DROPBOX and copy each matching source row to the next available destination row.

Column D is represented by the number 4 in Cells(i, 4). InStr with vbTextCompare performs a case-insensitive search, while the Or operator allows either keyword to trigger the copy.

Open the VBA editor

In Excel, press Alt+F11, choose Insert > Module, and open the module containing the existing macro.

Replace the search and copy logic

Use the following code, updating the worksheet names and the first data row if necessary: Sub CopyClockifyOrDropboxRows() Dim sourceSheet As Worksheet Dim targetSheet As Worksheet Dim lastRow As Long Dim nextRow As Long Dim i As Long Dim cellText As String Set sourceSheet = ThisWorkbook.Worksheets("Source") Set targetSheet = ThisWorkbook.Worksheets("Target") lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "D").End(xlUp).Row For i = 2 To lastRow cellText = CStr(sourceSheet.Cells(i, 4).Value) If InStr(1, cellText, "CLOCKIFY", vbTextCompare) > 0 _ Or InStr(1, cellText, "DROPBOX", vbTextCompare) > 0 Then nextRow = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row + 1 sourceSheet.Rows(i).Copy Destination:=targetSheet.Rows(nextRow) End If Next i End Sub

Run and verify the macro

Close the VBA editor, press Alt+F8, select CopyClockifyOrDropboxRows, and click Run. Confirm that rows with either keyword in column D appear on the Target sheet without overwriting existing rows.

Case-insensitive matching enabled: vbTextCompare matches CLOCKIFY and DROPBOX regardless of capitalization. If the destination sheet is empty, the next available row is calculated as row 2 because column A's last used row is treated as row 1.

Free Microsoft Office alternative

Move Excel Workbooks to WPS Office for Lightweight Spreadsheet Editing

WPS Office provides a free, lightweight spreadsheet environment with a familiar interface and strong compatibility with Microsoft Excel files. It supports seamless migration for reviewing, editing, and maintaining .xlsx and .xls workbooks, while Excel-specific VBA macro execution may still require Microsoft Excel.

1. Install WPS Office: Download and install WPS Office, then open WPS Spreadsheets from the desktop or Start menu.
2. Open the Excel workbook: Choose Menu > Open, select the .xlsx or .xls workbook, and review the source and destination sheets.
3. Check the copied rows: Use the destination sheet to verify that rows containing CLOCKIFY or DROPBOX were transferred correctly; run Excel VBA macros in Microsoft Excel when macro execution is required.

Free and lightweight spreadsheet software for reviewing and editing workbook dataFamiliar spreadsheet interface that makes migration from Excel straightforwardCompatible with Microsoft Excel .xlsx and .xls formatsUseful for checking filtered rows and destination-sheet results after a macro run

Frequently Asked Questions

Which VBA expression searches column D for CLOCKIFY?

Use InStr(1, sourceSheet.Cells(i, 4).Value, "CLOCKIFY", vbTextCompare) > 0. The number 4 refers to column D.

How can the macro match CLOCKIFY or DROPBOX?

Place two InStr conditions in one If statement and join them with Or: If InStr(1, cellText, "CLOCKIFY", vbTextCompare) > 0 Or InStr(1, cellText, "DROPBOX", vbTextCompare) > 0 Then.

How does the macro find the next available destination row?

Use targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row + 1. This finds the last used cell in column A and selects the following row.

What happens if the destination sheet is empty?

If column A is empty, End(xlUp).Row returns 1, so the first copied row is placed in row 2. Change the calculation if your destination sheet should begin in row 1.

Can the macro search a different source column?

Yes. Replace Cells(i, 4) with the appropriate column number, such as Cells(i, 5) for column E, and update lastRow to use the same column.

Excel & Data Problems