Excel - How To Automatically Move Rows In Excel When Marked As Paid - Episode 1903

4,744 views · Published 6 August 2014 · 5:45 · Indexed 21 October 2026

Channel: MrExcel.com · 2014 · Education

Watch on YouTube

Microsoft Excel Tutorial: Move record to another worksheet once the invoice is marked as paid.

Welcome back to the MrExcel podcast. In this episode, we will be discussing how to automatically move paid records to another worksheet in Excel. This is a common task that many Excel users encounter, and we will be using VBA to accomplish this task.

To begin, we have a data set on our original sheet and when a record is marked as paid (changing from N to Y), we want it to automatically move to the data sheet. We could potentially use an array formula to extract all the Y values to the other sheet, but in this case, we will be using VBA. Before we begin, it's important to note that our workbook is saved as an xlsx file, which does not allow macros. So, we will need to save it as an xlsm or xlsb file to enable macros.

If you are new to using macros, you will need to go to the Trust Center and make sure that macros are enabled. We recommend choosing the option to "Disable all macros with notification" to ensure the safety of your workbook. Now, let's get started with the VBA code. We will need to open the Visual Basic Editor by pressing Alt+F11 and then navigate to the Project Explorer. From there, we will select the original sheet and double click on it to open the code pane.

In the code pane, we will select "Change" from the top left drop-down menu and delete the existing code. This will create a new macro that will run every time a cell is changed on the worksheet. We will then use the variable "Target" to determine which cell was changed. In this case, we are looking for changes in column G, but you may need to adjust this based on the location of your data. We will then use an If statement to check if the value in the cell is "Y" (yes) and allow for both uppercase and lowercase entries. If the value is "Y", we will then move the record to the data sheet.

To determine where to move the record, we will need to find the next available row on the data sheet. We will use the variable "NR" to hold this value and use the "End(xlup)" function to find the last row with data and add 1 to it. We will also use the variable "FR" to hold the row of the record that was changed. Finally, we will use the "Copy" function to copy the record from the original sheet to the data sheet. This may require adjusting the number of columns depending on the location of your data.

Once the code is in place, we can test it out by changing a record from "N" to "Y" on the original sheet. You may not see any visible changes, but the record will automatically be added to the correct location on the data sheet. This simple VBA code can save you time and effort when dealing with large data sets in Excel.

Thank you for tuning in to this episode of the MrExcel podcast. We hope this tutorial has been helpful in learning how to move paid records to another worksheet in Excel. Be sure to check out our other episodes for more Excel tips and tricks. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

Table of Contents:
(00:00) Moving Paid Records to Another Worksheet
(00:23) Solution Using VBA
(00:35) Saving the Workbook as a Macro-Enabled File & Enabling Macros
(01:07) Identifying the Column for Paid Records
(01:18) Accessing the Worksheet Code Pane
(01:36) Choosing the Appropriate Worksheet Event & Writing the Macro
(02:40) Moving the Record to the Data Worksheet
(04:00) Testing the Macro
(05:24) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #excelvba

This video answers these common search terms:
how to automatically move rows in excel
automatically transfer data to another sheet when marked as paid
can you automatically move data from one sheet in excel to another
excel move row to another worksheet when status is paid
how to make data automatically move to tab in excel
move data to a different sheet in excel when paid
excel move row to a new sheet when marked as paid
how to move rows in excel automatically
how to move row to another sheet excel
auto-archiving paid records to a new worksheet in excel
excel automate record transfer based on status
excel move record to new worksheet on condition
conditional data transfer in excel to a new sheet

When you mark a record as Paid, can you automatically copy that record to another worksheet? Today's episode shows an event handler macro that will move records after they have been marked as Paid.

Join the MrExcel Message Board discussion about this video at  https://www.mrexcel.com/board/threads/1153730/

More from this channel