How to move a certain row into a new sheet when a checkbox is checked in Excel?
In daily Excel usage, there are often scenarios where users need to move a row to another sheet or worksheet depending on whether a checkbox is checked, such as task status lists, order processing forms, and data review checklists. By automating this process, you can enhance workflow efficiency and maintain a clear overview of your data. This article outlines multiple practical solutions for moving a specific row to a new sheet when a checkbox is checked, catering to different operational preferences and data management needs.
Move row when a checkbox is checked with VBA code
If you want to move a certain row to a specified worksheet automatically when a checkbox is checked, Excel's VBA (Visual Basic for Applications) can provide a highly customizable solution. This method is especially useful for users handling large datasets or frequently repeating the operation.
1. Open the worksheet where you need to move rows based on a checkbox status. Navigate to Developer > Insert > Checkbox (ActiveX Control) to insert a checkbox into your sheet. This control allows you to link actions to the checkbox. See screenshot:
2. Right-click on the worksheet tab at the bottom of your Excel window, then choose View Code from the context menu to open the corresponding code window for the sheet. See screenshot:
3. In the Microsoft Visual Basic for Applications window that appears, copy and paste the following VBA code into the code window:
VBA code: Move row into a specified worksheet when a checkbox is checked
Private Sub CheckBox1_Click()
Dim xRg As Range
Dim xAddress As String
On Error Resume Next
If CheckBox1.value Then
xAddress = Application.ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("Please select the range row you will move(single cell)：", "KuTools For Excel", xAddress, , , , , 8)
If xRg Is Nothing Then Exit Sub
Set xRg = xRg(1).EntireRow
xRg.Copy
ActiveWorkbook.Sheets("Sheet3").Range("A1").PasteSpecial xlPasteAllUsingSourceTheme
xRg.Clear
Application.CutCopyMode = False
End If
End SubNotes: In the code, Sheet3 and A1 indicate that the specified row will be moved to the first row of Sheet3. Please change them based on your needs. For example, to move the row to Sheet2 starting at B10, simply adjust these values in the code.
Be careful when editing the macro. Making incorrect changes may cause errors or unintended data movement. Always save your workbook before running new VBA code in case you need to undo changes.
4. After entering the code, press Alt + Q on your keyboard to exit the VBA editor and return to your Excel worksheet.
5. Before using the functionality, make sure to turn off Design Mode in the Developer tab. This step activates the checkbox control so it can trigger the VBA macro as intended.
6. Now, click the checkbox you added. A Kutools for Excel dialog box will pop up, prompting you to select the first cell of the row or the entire row you wish to move. Once selected, click OK. The specified row will instantly be moved to the designated worksheet as defined in your VBA code. See screenshot:
Excel Formula - Flag and filter rows for moving
A formula-driven solution can help you quickly identify which rows should be moved when their associated checkbox is checked. This is especially practical in data entry tables where checkboxes are linked to helper columns. These flagged rows can then be filtered and pasted into another sheet manually. The method does not require VBA and is ideal where strict automation isn't needed or macro use is restricted.
Assuming you have checkboxes in B2 to B100 and linked to cell C2 to C100, and your data rows are in A2:B100, you can use a helper column (for example, column D) with a formula to flag checked rows:
1. In cell D2, enter the following formula:
=IF(C2=TRUE, "Move", "")2. Press Enter to confirm the formula, then copy it down to cover all data rows (e.g., D2 to D100). You can do this by dragging the fill handle or by double-clicking it at the bottom right corner of the cell.
3. Now you can filter column D to show only "Move", then select the visible (filtered) rows and copy them to your target worksheet using Ctrl+C and Ctrl+V.
Tip: If your checkboxes are not linked to cell values, right-click each checkbox and set its Cell Link property so checking or unchecking updates a cell to TRUE or FALSE. This will enable the formula to work as intended.
Pros: No need for macros and works in shared files. Cons: Some manual steps are required for moving flagged rows.
Related articles:
- How to move entire row to another sheet based on cell value in Excel?
- How to move entire row to the bottom of active sheet based on cell value in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in