Adding checkboxes in Microsoft Excel opens up new ways to organize data and automate your workflow. With a few simple steps, you can use checkboxes to move rows between worksheets, making things like task lists and invoice tracking much easier. This guide covers practical examples for both project management and business, all while keeping your data organized and up to date.

Checkboxes bring flexibility to a wide range of Excel tasks, but here are two of the most effective uses:

  • Project management: Automatically move completed tasks to a “Completed” sheet.
  • Business tracking: Instantly shift paid invoices to a “Paid” worksheet.

No more copying and pasting rows! This saves time and reduces errors.

Preparing Your Excel Workbook: Adding Checkboxes

Start by setting up your worksheet. Place a checkbox in each row you want to track (column A works best).

  • Unchecked box: Formula bar says FALSE, meaning the item is not completed or paid.
  • Checked box: Formula bar says TRUE, meaning the item is done or paid.

Simply checking the box won’t move data on its own. You’ll need a small VBA script to connect the checkboxes to your workflow and move the rows between worksheets.

Opening the Visual Basic Editor

Open the Visual Basic Editor to add the code by pressing Alt + F11. This tool manages VBA scripts that make Excel do more. If you have several workbooks open, make sure you highlight the correct one before moving forward.

RELATED TOPIC: How to Import Multiple CSV Files Into Separate Worksheets in Excel

Walking Through the VBA Code Logic

The VBA code at the heart of this process is simple but powerful. Here’s the flow:

  1. If a checkbox is checked (TRUE), move the entire row from the source sheet (like “Tasks”) to the target sheet (like “Completed”).
  2. If a checkbox is unchecked (FALSE) in the destination sheet, move the row back to the bottom of the source sheet.
  3. After copying a row, delete it from the original spot. The code always places the row at the bottom of the new sheet for easy tracking.

This helps keep each sheet clear and up to date, even if tasks or invoices need to move back and forth.

Pasting and Setting Up the VBA Code

In the Visual Basic Editor, choose the right workbook on the left pane. Click ThisWorkbook, then paste the provided VBA code. Consider a helpful comment at the top (like “Move completed task to completed worksheet”) for clarity in the future.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim srcSheet As Worksheet, destSheet As Worksheet
    Dim checkCell As Range, moveRow As Range
    Dim lastRow As Long
    Dim direction As String

    ' Only handle changes in Column A
    If Intersect(Target, Sh.Columns("A")) Is Nothing Then Exit Sub
    If Target.Cells.CountLarge > 1 Then Exit Sub

    Application.EnableEvents = False

    Set checkCell = Target
    Set moveRow = checkCell.EntireRow

    If checkCell.Value = True Then
        ' Move from Tasks to Paid
        Set srcSheet = ThisWorkbook.Sheets("Invoices")
        Set destSheet = ThisWorkbook.Sheets("Paid")
    ElseIf checkCell.Value = False Then
        ' Move from Completed back to Invoices
        Set srcSheet = ThisWorkbook.Sheets("Paid")
        Set destSheet = ThisWorkbook.Sheets("Invoices")
    Else
        GoTo ExitHandler
    End If

    ' Ensure we're acting on the correct sheet
    If Sh.Name <> srcSheet.Name Then GoTo ExitHandler

    ' Copy row to destination sheet
    lastRow = destSheet.Cells(destSheet.Rows.Count, "A").End(xlUp).Row + 1
    moveRow.Copy Destination:=destSheet.Rows(lastRow)

    ' Delete original row
    moveRow.Delete

ExitHandler:
    Application.EnableEvents = True
End Sub

Save as Macro-Enabled Workbook

Don’t forget this step or your automation of moving rows between worksheets won’t work:

  1. Go to File.
  2. Click Save a Copy.
  3. Select a destination for your file.
  4. Choose Excel Macro-Enabled Workbook (*.xlsm).
  5. Click Save.

Your macros will be saved and ready to run every time you open the workbook.

RELATED TOPIC: Boost Productivity with Excel’s New Checkboxes

Example 1: Task List Automation in Action

Imagine a task list with columns for the task, due date, priority, and person assigned, plus a checkbox in column A. When you check a box to mark a task as done, that row moves to the “Completed” sheet. If you uncheck it there, it slides back to the bottom of the main task sheet. It’s a simple way to keep “open” and “completed” records separated with just a click.

Example 2: Automated Invoice Tracker

Now swap tasks for invoices. Set up a worksheet with these columns:

  • Invoice number
  • Date
  • Due date
  • Amount
  • Customer
  • Checkbox

With the same code logic, checking the box next to an invoice moves the rows it to the “Paid” worksheet. Unchecking returns it to “Invoices,” always placing it at the bottom. You can check multiple invoices together for batch updates.

Tips and Best Practices for Moving Rows Between Worksheets

  • You can use checkboxes for countless scenarios where you want to move rows between sheets.
  • When an item moves back, it’s always added to the bottom, not to its original place.
  • Always work with a macro-enabled workbook and save your changes frequently.

Get More LibreOffice Tutorials

Subscribe to my YouTube channel for more LibreOffice tutorials.

Follow me on X for updates and quick tips. I also participate in LibreOffice discussions on Reddit.

New to LibreOffice? Start here

This page and website may contain affiliate links. These are tools I personally use and love. I may receive a small commission should you make a purchase using one of these links. This helps keep the tutorials and website posts coming and up to date. Thank you for your support! For more information please see my full disclaimer.

RELATED ARTICLESMORE FROM AUTHOR