Note System Excel Macro – Organised Notes in Tabular Format!
This awesome Excel template processes your notes and stores them in an easy to review tabular format.
Taking notes is easy. Most people do it, whether you are in class or at work. Some use laptops/phones, others use an actual notepad. The problem most people have, myself included, is that you never go back to review the notes you took!
This problem is what lead to the creation of this template; notes in a tabular format are easy to analyse and review, plus it’s a breeze to code automation such as “remind me” functions when the data is in a clean format.
So read on, learn how to write the simple macro which you can then deploy in your spreadsheet software of choice (this would also work in Google Sheets), or skip below to download the free Note System Macro Template!
Steps
1. Create input form template
2. Create helper columns
3. Setup the start of your VBA code
4. Create the body of your VBA code
5. Perfect the code with a Delete function
Create the Input Form Template
We will start by designing the Excel sheet. You can deviate from my instructions below, but you have to make sure that if the input cells are in different locations, you update the VBA code to match. Cosmetic changes, such as color, font, titles, feel free to change as much as you like!
1. Open up Excel.
2. Create a new “Blank workbook”.
3. Go to “File”, and click “Save As”.
4. Click “Browse”, name it “Note System” and make sure you select “Excel Macro-Enabled Workbook”.
5. Rename “Sheet 1” to “Notes”.
We can now start designing the form, I will leave all the format default, but here is where you would use your creative flair.
6. In cells C5 to C7, write “Date”, “Week”, “Site”.
7. In cell C9, write “Technical Details” and merge it to cell C12. Copy and paste 7 of these, naming them whatever you desire.
8. In the 3 columns (D to F) to the right of your header cells (the cells you just merged, plus the date, week and site), merge these as well to create an input box shape.
9. Expand the column width until you think the input boxes look acceptable.
10. In cell G8, write “Remind Me?”. Merge cells below to create an option for each category.
11. Add in some data validation in the “Remind Me?” field. Select all the cells, go to “Data” ribbon, “Data Validation”. Change “Allow” to “List”. Write “Yes,No” in “Source”.
12. Change all the “Remind Me?” fields to No, which will be their default state.
We now have our basic input form, feel free to add in some more validation (for example maybe on the “Site” field). All we have left on our template is the table where the data will be copied to!
13. In cell C49 through to H49, write “Date”, “Site”, “Week”, “Subject”, “Remind Me”, “Notes”. You can name these anything you want, but the ones below will match the headers we created on our input form.
14. Draw borders around the table to make it reach row 3049. This will give the table 3000 rows, and the borders will allow you to keep track of how much space you have left and help you program the VBA code.
15. In cells G46 and G47, write “Table Length” and “Table Limit”. In “Table Limit”, write 3000. In “Table Length”, put in the formula to count non blank rows =COUNTA($C$50:$C$3052)
We have now created our simple form and table! I’ve left a few rows and columns empty for a reason; since we will be referencing exact cells in VBA, it’s a good idea to leave some space in case you want to create more features. As inserting columns/rows would wreck the VBA code.
Add in Helper Columns to make the VBA Coding Easier!
We are now going to add in some helper columns. While we could forgo this and do it all in VBA, I find it easier to have the helper columns do most of the work. As they are more robust to added row/columns, and are (arguably) easier to understand.
First we are going to add a formula where we can figure out which input fields are empty, and which are full.
1. In cell I8, write “1 If Text”.
2. In cell I9, add the formula - =IF(ISBLANK(D9),2,1) . If D9 (category 1) is blank, return 2, else 1.
3. Copy the formula to I13, I17, I21, I25, I29, I33. Make sure D9 in the formula is changing when you are copying the formula.
4. In cell I35, write “All cells empty?”. In the cell below, sum up the formulas you just added above. It should come to 14, which will tell our VBA code there is no data so don’t run.
Due to the merged cells, we now are going to link our input cells to another helper column. If we get the macro to copy from the merged cells, when it pastes it will include the extra cells in the merge. We don’t want this for our table.
5. Link to the date input in I5, week input in I6, site input I7 and the other input columns alongside the formulas above in columns K to M.
To finish our template, will add in two buttons.
6. Go to “Developer” ribbon, “Insert”, “Button”.
7. Draw the button below your input form, click okay when the macro window pops up, and right click on the button to call it “Submit Note”.
8. Draw another button above your data table, called “Delete Last Entry”.
That’s the note system template, and most fiddly part done! I suggest you now stop, and color the template to make it more presentable.
Create the Start of the VBA Code
The code is designed to be modular, inspired by the different categories we have. We define some variables, and then can easily add/remove categories.
1. Go to the “Developer” ribbon and click on “Visual Basic".
2. Right click on “ThisWorkbook” and insert a new module.
3. Copy in the following code:
Sub Notesystem()
'Setup------------------------------------------------------------------------------------------------------------------------------------
'Turn off screen updating - this speeds up the script and prevents screen flicker
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Activate the right workbook and sheet. **YOU NEED TO CHANGE THIS IF YOU RENAME THE WORKBOOK OR SHEET**
Workbooks("Note System.xlsm").Sheets("Notes").Activate
'Tell Excel what the variables are. We have to use objects for our variables, as these are used as a formula. If we were after just the number from a cell, we would not have to do this.
Dim DateI As Object 'Date Input
Dim WeekI As Object 'Week Input
Dim SiteI As Object 'Site Input
Dim CategoryI As Object 'Category Input
Dim CategoryN As Object 'Category Name
Dim CategoryH As Object 'Helper Column
Dim RemindMeI As Object 'Category Input
Dim AllCellsEmpty As Integer 'Helper Column
'Link the defined variables to a specific range with an integer
Set DateI = Range("I5")
Set WeekI = Range("I6")
Set SiteI = Range("I7")
AllCellsEmpty = Range("I36").Value
'If all the cells are empty, then display message and skip everything else
If AllCellsEmpty = 14 Then
MsgBox "No data to copy"
ElseThe comments from the code do a decent job at explaining what they are doing. We have to define most of the variables as objects, because I am using them as a part of a longer function. Contrast to AllCellsEmpty, which I’m just using as a number.
In the script, we define what data types the variables are, then set some of the variables to cells on the Excel sheet. Finally, if AllCellsEmpty = 14, we will display a message box and end the script.
Create the Body of the VBA Code
We now need to add in the body of the code. I have made it like a module, where categories can be added and removed. You just need to change the cell locations. The body simply grabs the data and copies it to the table.
1. Copy in the following code:
'Category1--------------------------------------------------------------------------------------------------------------------------------
Set CategoryI = Range("L9")
Set CategoryN = Range("K9")
Set RemindMeI = Range("M9")
Set CategoryH = Range("I9")
'If CategoryH is 1. CategoryH has a formula in it, which returns 2 if there isn't text, 1 if there is.
If CategoryH.Value = 1 Then
'Copy first category
CategoryI.Select
Selection.Copy
'This finds the first empty row in the table for notes
Range("H3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the remind me cell for category
RemindMeI.Select
Selection.Copy
'This finds the first empty row in the table for remind me
Range("G3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the name of the category
CategoryN.Select
Selection.Copy
'This finds the first empty row in the table for subject
Range("F3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the date
DateI.Select
Selection.Copy
'This finds the first empty row in the table for date
Range("C3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the week
WeekI.Select
Selection.Copy
'This finds the first empty row in the table for week
Range("E3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the site
SiteI.Select ' House
Selection.Copy
'This finds the first empty row in the table for site
Range("D3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End If
'Category2--------------------------------------------------------------------------------------------------------------------------------
Set CategoryI = Range("L13")
Set CategoryN = Range("K13")
Set RemindMeI = Range("M13")
Set CategoryH = Range("I13")
'If CategoryH is 1. CategoryH has a formula in it, which returns 2 if there isn't text, 1 if there is.
If CategoryH.Value = 1 Then
'Copy first category
CategoryI.Select
Selection.Copy
'This finds the first empty row in the table for notes
Range("H3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the remind me cell for category
RemindMeI.Select
Selection.Copy
'This finds the first empty row in the table for remind me
Range("G3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the name of the category
CategoryN.Select
Selection.Copy
'This finds the first empty row in the table for subject
Range("F3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the date
DateI.Select
Selection.Copy
'This finds the first empty row in the table for date
Range("C3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the week
WeekI.Select
Selection.Copy
'This finds the first empty row in the table for week
Range("E3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the site
SiteI.Select ' House
Selection.Copy
'This finds the first empty row in the table for site
Range("D3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End If
'Category3--------------------------------------------------------------------------------------------------------------------------------
Set CategoryI = Range("L17")
Set CategoryN = Range("K17")
Set RemindMeI = Range("M17")
Set CategoryH = Range("I17")
'If CategoryH is 1. CategoryH has a formula in it, which returns 2 if there isn't text, 1 if there is.
If CategoryH.Value = 1 Then
'Copy first category
CategoryI.Select
Selection.Copy
'This finds the first empty row in the table for notes
Range("H3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the remind me cell for category
RemindMeI.Select
Selection.Copy
'This finds the first empty row in the table for remind me
Range("G3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the name of the category
CategoryN.Select
Selection.Copy
'This finds the first empty row in the table for subject
Range("F3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the date
DateI.Select
Selection.Copy
'This finds the first empty row in the table for date
Range("C3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the week
WeekI.Select
Selection.Copy
'This finds the first empty row in the table for week
Range("E3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the site
SiteI.Select ' House
Selection.Copy
'This finds the first empty row in the table for site
Range("D3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End If
'Category4--------------------------------------------------------------------------------------------------------------------------------
Set CategoryI = Range("L21")
Set CategoryN = Range("K21")
Set RemindMeI = Range("M21")
Set CategoryH = Range("I21")
'If CategoryH is 1. CategoryH has a formula in it, which returns 2 if there isn't text, 1 if there is.
If CategoryH.Value = 1 Then
'Copy first category
CategoryI.Select
Selection.Copy
'This finds the first empty row in the table for notes
Range("H3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the remind me cell for category
RemindMeI.Select
Selection.Copy
'This finds the first empty row in the table for remind me
Range("G3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the name of the category
CategoryN.Select
Selection.Copy
'This finds the first empty row in the table for subject
Range("F3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the date
DateI.Select
Selection.Copy
'This finds the first empty row in the table for date
Range("C3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the week
WeekI.Select
Selection.Copy
'This finds the first empty row in the table for week
Range("E3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the site
SiteI.Select ' House
Selection.Copy
'This finds the first empty row in the table for site
Range("D3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End If
'Category5--------------------------------------------------------------------------------------------------------------------------------
Set CategoryI = Range("L25")
Set CategoryN = Range("K25")
Set RemindMeI = Range("M25")
Set CategoryH = Range("I25")
'If CategoryH is 1. CategoryH has a formula in it, which returns 2 if there isn't text, 1 if there is.
If CategoryH.Value = 1 Then
'Copy first category
CategoryI.Select
Selection.Copy
'This finds the first empty row in the table for notes
Range("H3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the remind me cell for category
RemindMeI.Select
Selection.Copy
'This finds the first empty row in the table for remind me
Range("G3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the name of the category
CategoryN.Select
Selection.Copy
'This finds the first empty row in the table for subject
Range("F3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the date
DateI.Select
Selection.Copy
'This finds the first empty row in the table for date
Range("C3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the week
WeekI.Select
Selection.Copy
'This finds the first empty row in the table for week
Range("E3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the site
SiteI.Select ' House
Selection.Copy
'This finds the first empty row in the table for site
Range("D3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End If
'Category6--------------------------------------------------------------------------------------------------------------------------------
Set CategoryI = Range("L29")
Set CategoryN = Range("K29")
Set RemindMeI = Range("M29")
Set CategoryH = Range("I29")
'If CategoryH is 1. CategoryH has a formula in it, which returns 2 if there isn't text, 1 if there is.
If CategoryH.Value = 1 Then
'Copy first category
CategoryI.Select
Selection.Copy
'This finds the first empty row in the table for notes
Range("H3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the remind me cell for category
RemindMeI.Select
Selection.Copy
'This finds the first empty row in the table for remind me
Range("G3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the name of the category
CategoryN.Select
Selection.Copy
'This finds the first empty row in the table for subject
Range("F3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the date
DateI.Select
Selection.Copy
'This finds the first empty row in the table for date
Range("C3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the week
WeekI.Select
Selection.Copy
'This finds the first empty row in the table for week
Range("E3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the site
SiteI.Select ' House
Selection.Copy
'This finds the first empty row in the table for site
Range("D3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End If
'Category7--------------------------------------------------------------------------------------------------------------------------------
Set CategoryI = Range("L33")
Set CategoryN = Range("K33")
Set RemindMeI = Range("M33")
Set CategoryH = Range("I33")
'If CategoryH is 1. CategoryH has a formula in it, which returns 2 if there isn't text, 1 if there is.
If CategoryH.Value = 1 Then
'Copy first category
CategoryI.Select
Selection.Copy
'This finds the first empty row in the table for notes
Range("H3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the remind me cell for category
RemindMeI.Select
Selection.Copy
'This finds the first empty row in the table for remind me
Range("G3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the name of the category
CategoryN.Select
Selection.Copy
'This finds the first empty row in the table for subject
Range("F3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the date
DateI.Select
Selection.Copy
'This finds the first empty row in the table for date
Range("C3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the week
WeekI.Select
Selection.Copy
'This finds the first empty row in the table for week
Range("E3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copies the site
SiteI.Select ' House
Selection.Copy
'This finds the first empty row in the table for site
Range("D3049").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End If
'Saves and clears form for new data---------------------------------------------------------------------------------------------------------
ActiveWorkbook.Save
Range("D9:D34").Value = ""
Range("G9:G34").Value = "No"
MsgBox "Added and Saved!"
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End SubThe only things you need to change, is CategoryI, CategoryN, RemindMeI and CategoryH. Plus, Range("D3049").Select if you ever decide to extend the length of the table.
All this macro does is selects the cell with the data, copies it, goes to the bottom of the table, pushes ctrl + arrowkey up, moves down 1 row, and pastes the data. Finally, it saves the workbook and deletes the data from the input form.
Finish off with a delete function
Finally, let’s add in a macro that will delete rows of the table.
1. Copy in the following code:
Sub LastEntryDeleter()
'If the the table length is greater than 0
If Range("H46").Value > 0 Then
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'This finds the last row with content, clears the cell, moves to the left, clears the cell etc
Range("C3052").Select
Selection.End(xlUp).Select
Selection.ClearContents
Selection.Offset(0, 1).Select
Selection.ClearContents
Selection.Offset(0, 1).Select
Selection.ClearContents
Selection.Offset(0, 1).Select
Selection.ClearContents
Selection.Offset(0, 1).Select
Selection.ClearContents
Selection.Offset(0, 1).Select
Selection.ClearContents
Selection.Offset(0, 1).Select
Selection.ClearContents
Range("C5").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Else
MsgBox "No rows to delete"
End If
End SubThe comments again explain what is going on here; very simple as I made this mostly with the macro recorder.
One more thing before we can test out the template!
2. Go back to the template, right click on each button and click “Assign Macro”. Click on appropriate macro.
That’s it! Note taking template complete!
You can easily take notes and enjoy the benefits of a nice structured table! You can customize it to suit your needs, for example changing the input field names, removing/adding fields etc. The Remind Me field can be utilized using a formula, or through an Auto Email Macro.
The macro can be optimised a fair bit, for example you could name ranges to make the variable definition more simple, you could rewrite the copy functions to make there be no need for object variables. However it works, and I prefer simple complicated over complicated simplicity.
Why not challenge yourself, and see if you can get this to work on Google Sheets? Start off by using my Google Sheet Macro Tutorial. Or if you prefer to stay in Excel, why not utilize my Macro Scheduling tutorial in conjunction with above email macro.
Download the template for free, below!
4. Download
More templates
