Integrate Python with Excel – Refresh links and run macros, perfect for SharePoint!

If you are getting into Python, chances are you started with Excel Macros and are looking to find a more powerful tool. Python is fantastic, easier than VBA and almost limitless possibilities.

However starting something new is hard if there is no specific objective - which is why starting to learn Python with Excel as a crutch is a great first step. Slowly overtime your increased Python skills can move you further away from Excel.

So let’s start by having some simple, reliable, Python code to automate an Excel task that has given me issues in the past. Namely, refreshing queries on a workbook.

Our code will open an Excel workbook, refresh all queries, wait for them finish, run Excel Macro, close the workbook.

 

Steps

1. Write Macro

2. Write Python Code

3. Create Batch file

4. Done!

 

Create the Excel Macro

This can be any Macro you would like, all we need to do is make sure it works and that we remember the name of the macro.
For this example I will use my Excel Email macro, as emailing with Python is possible but as of writing this I am struggling to figure out an easy way to paste specific range from Excel to the body of an Outlook email.

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”.

create-workbook-note-system

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”.

date-week-site.jpg

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.

create-input-form-template.jpg

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.

add-remind-me-column-and-date-validation.jpg

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.

create-table-headers.jpg

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)

simple-excel-note-template.jpg

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.

Create Python code

We will now create our Python code. I’m assuming you have a basic knowledge of Python - as in having it installed and your favorite editor ready to go. I use Pycharm and there is only one package that you need to install in the code below.

If you are new to Python, it’s super simple. You just need to install Python (which just seems like nonsense until you get an editor), install an editor (highly recommend Pycharm Community Edition), point your project to the Python.exe file which is in the directory of Python you just installed, and you’re ready to go.

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.

helper-column-for-macro-run-if-text.jpg

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.

finished-helper-columns-for-note-system.jpg

To finish our template, will add in two buttons.

6.      Go to “Developer” ribbon, “Insert”, “Button”.

inset-button.jpg

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”.

10.jpg

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.

cleaned-up-note-taking-template.jpg

Create the Batch file

Python doesn’t need to run via the editor. We are going to create a batch file that makes the code easy to run and more efficient than opening up the big editor.
This also allows us to schedule run times, as detailed in my Macro Scheduling tutorial.

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:

The 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 Sub

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 Sub

The 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!

note-system-template-excel-finished.gif

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