Excel - Macro to Add Daily Entries to Monthly Total - Episode 1973

Microsoft Excel Tutorial: Macro to add daily entries to a total.

Welcome to the MrExcel netcast, where we help you learn Excel through our podcast episodes. In today's episode, number 1973, we will be discussing how to automatically add daily entries for a running total in Excel. This question was sent in by John via Twitter, and we will be showing you two different ways to achieve this task.

John's question is about adding daily entries to a total column in Excel. He wants the cost of items to be added to the "Daily Entries" column, and then have that value automatically added to the pre-existing total. However, he also wants the "Daily Entries" column to remain blank for the next entry. This may seem like a simple task, but it can become complicated if you make a mistake and need to undo it.

In this episode, we will show you two ways to achieve this task. The first method is to immediately add the entries to the total column, and the second method is the way that I would personally do it. To use the first method, you will need to make sure that your workbook is saved as an xlsm or xlsb file. Then, we will go into VBA by pressing Alt+F11 and selecting the "View Code" option. From there, we will change the "SelectionChange" option to "Change" so that the macro code runs every time a change is made on the worksheet.

Next, we will check if the change was made in the designated range (B2:B15 in this case) and if it was, we will add the value to the total column and clear the original cell. However, this method has a downside - it wipes out the undo option. So, if you make a mistake, you will have to manually correct it. This is why I prefer the second method, which involves entering all the values for the day and then using a macro to move them to the total column at once. This way, you can double-check for any mistakes before moving the entries.

To use this method, we will insert a module and create a simple macro called "Sweep" which will move the entries from the "Daily Entries" column to the total column. We will then assign this macro to a shape on the worksheet, making it easy to run the macro whenever needed. This method may take a little more time, but it is a lot safer and reduces the chances of making a mistake.

In conclusion, while it is possible to immediately add daily entries to a total column in Excel, it is safer to use a macro to move the entries at once. This way, you can avoid any mistakes and have a more accurate total. If you are interested in learning more about VBA, be sure to check out our book co-authored by Tracy and myself. Thank you for watching this episode of the MrExcel netcast, and we will see you next time for more Excel tips and tricks!

Table of Contents:
(00:00) Automatically adding daily entries for running total
(00:30) Two ways to add entries immediately or using a macro
(01:03) Saving workbook as xlsm and going into VBA
(01:26) Worksheet Change Event Handler
(01:45) Intersect function in Excel VBA
(02:04) Turning off and on events to avoid losing undo option
(02:34) Mistakes can not be undo
(03:00) Preferred method of entering all values for the day and sweeping them over to total
(04:19) 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:
add daily entries to running total
how to run a macro
run a macro after every entry
event handler macro in excel
lose undo after macro in excel

John asks a common question: When I enter a value in B, can you immediately add the value to C and clear out B for the next entry? While this can be done, the loss of Undo makes it risky. I show a VBA macro to do immediately and also to sweep a batch of entries.

[davidfunvideos]

I am not sure why he wanted to do but another approach would be to have the totals be a sumif or sumifs. Then have the macro add it to data in another work sheet ( a named range) with a date column, category and the entry number then have the macro adjust the size of the named range. That way you can back track if you find a error.

[kuffourappianin]

How do you do for multiple columns, assuming you enter a value for B, D, F, H. how do you go about it

[nikunjgattani]

Finding whether file contains duplicate value for id column ?

Hi,

I have more than 100 excel file in a folder. I want to check whether a particular column (id ) values are duplicate or not in each file.
If there are duplicate ids in particular file. I will get the name of file/files which is/are having duplicate values.

Opening each file and finding duplicate is tedious work. Do we have a VBA code to do the same ?

Regards,
Nikunj

[ExcelStrategy]

The Macro it is clean and effective but from a strictly logical point of view it is completly not recomended to delete the daily entries, there must be a worksheet that keeps tracks of the past entries.

[ashaydwivedi]

I Too Prefer Method 2. You Din't Show Method 3 Sheet. Why?