How to automate your spreadsheets with Excel macros

exgap (54)in #microsoftexcel • 5 years ago (edited)

I want to teach you with this article on how to join multiple tasks into a single one-second event and this way automate a lot of mundane time-consuming task in your excel workflow.

Excel is a fast working application for executing various calculations, accounting, analysis, visualization, etc. In fact, it takes one-tenth of a second to calculate a large spreadsheet, nevertheless, it's the manual work of typing and formatting that slows you down your workflow. That is the reason you want Excel macros to connect multiple tasks into a single action.

In this article you will learn:
• Tips for using an Excel macro
• How to prepare a master dataset for a macro environment
• How to automate data collection with macro
• How to automate data sorting with macro
• How to automate formulas with macro

I have given a sample workbook for the for the scenarios described in this article so you can follow along step by step. If needed, you can create a custom spreadsheet with your values in it.

Getting started

Let's begin with our first macro. Once you understand how easy it is to automate tasks using macros, you’ll never look back. But, before we jump in, here are some tips on how to manage your data before implementing a macro function:

Begin your macro at the Home position (Ctrl+Home keyboard shortcut)
Use Shortcut keys to expedite movement: Ctrl+Up, Strl+Down, Ctrl+Left, Ctrl+Right, Ctrl+Home, Ctrl+PageUp, Ctrl+PageDown
Keep your macros on a small scale and focus on a single task. This is a beneficial workflow for testing, editing, debugging, etc. You can combine multiple small macros into a big macro at any time.
Macros require "relative" cell references in opposed to "absolute" references. This basically means that you are pointing to a cell rather than hard-coding a cell position. This is due to the dynamic and constantly changing nature of spreadsheets.
Static values which rarely change, such as Names, Addresses, SKU numbers, etc. are usually entered before creating a macro function and they are not part of the function.
We have to prepare our data so we can work with, this means adding, editing or deleting unnecessary records.

Why are small macros important

For this example, we have an eCommerce store owner who has expanded his territory from a single store to 8 different major cities. Now the CEO has been running his own books for years, which was a challenging task for a single store, and now he has 8. He has to collect data from each store and merge it to monitor the progress of the entire company.
We built a few smaller macros to accomplish the following tasks:

Collect and combine data from 8 different stores into one master workbook
Organize and sort data
Enter formulas to calculate our merged data

Once small macros are built and tested, we can merge them into one master macro or just leave them as independent smaller macros. Either way, it is advised to keep smaller macros because it's much easier to work and debug them in contrast to big and detailed macros.

Preparing the Master spreadsheet

The first thing to remember is that In case you are building a spreadsheet from scratch, always start building your master spreadsheet first.
In the following chapter, I will guide you through a step by step process of creating a master spreadsheet:

Row 1: Enter the date formula in A1 and the store location in B1. See image below:
A1: =Today()
B2: Branch name and number
Row 2: Once the dynamic date and static info are entered, leave the entire second row reserved for the necessary calculations of every column. This is a good practice because this row is fixed and always visible.
Row 3: Enter the category names and any other field-specific information you would need. In the example below, they are extended from A3 through J3.
Column A: Enter the static data in column A. (values in your spreadsheet that rarely change). If your project works with product numbers or ID codes (in our example SKU numbers) or other unique value per product, enter them in Column A beginning from row 4 (don’t skip to row 5). Other static data fields might include the Product Description, the Product Price, sales tax percentage, etc. - these values can be placed in separate columns.

Every row in column A must include a unique value for two main reasons:
a) Column A is the main navigational column for our macro. It navigates through the spreadsheet based on the Home (A1) position and column A.
If some of the cells contain a blank field, it will disrupt the macro function.
b) If you want to build multiple tables later and connect them through Pivot Reports, you must have unique values from each table to properly join them.
There is a usual standard location for each category but this depends on your type of data.
a) Column B = Product Description
b) Column C = Quantity sold
c) Column D = Product price
e) Column E = Extended Cost
f) Column F = Price Discounts
g) Column G = Sales Tax
h) Column H = Totals
Remember, the total values of these fields are in the static row 2 for each column.
Once the master database structure is set up, do not change anything. If there's a need of adding additional columns to the table, use the insert column command.
For example, if you want to add a second discount level, position your cursor anywhere on column G and click the tab: Home > Insert > Insert Sheet Columns. This process does not affect the macro function.
The same rule applies to rows. Be careful when inserting rows inside an active. For instance, if the formula in row 2 states =SUM(B3:B20) and you insert a row outside of the formula’s range like B21, the new record won't be included in the formula's calculation and therefore you have to modify it to =SUM(B3:B21)
Let's set up the formula range to include all the current and possible future rows, this way you won't need to change this in the future.
a) C2: =SUM(C4:C500)
b) E2: =SUM(E4:E500)
c) F2: =SUM(F4:F500)
d) G2: =SUM(G4:G500)
e) H2: =SUM(H4:H500)

As seen above, we set the formula range to be several rows beyond our last record (500), so if there's ever a need to add new records, we can do that easily and not worry about adjusting the range.
At this stage, let's begin entering the formulas to calculate the different values in these columns:
a) E4: =SUM(C4 * D4), then copy from E4 down to E5:E500
b) F4: =SUM(E4 * 10%), where 10% represents the current discount percentage, then copy from F4 down to F5:E500
c) G4: =SUM(E4-F4) * 6.25%, where 6.25% represents the current Sales Tax in your country, then copy from G4 down to G5:G500
d) H4: =SUM(E4-F4+G4), then copy from H4 down to H5:E500
After all the spreadsheet formulas are inserted, all you will have to do is enter the sold quantity for each product in column C (daily, weekly, or monthly). If the product prices change, enter the new figures in column D. The foundation of this database is all formulas and static information.
Once we established our spreadsheet with a proper structure, our fixed data, and correct formulas, make 8 copies in worksheets 2 through 9. Rename every worksheet at the bottom to identify every store and change the name of the sheet1 work to 'Master'.
Change the location data on row 1 to identify the store information (that matches the store on the tab) on all 9 spreadsheets. In this step you can email a copy of each branch spreadsheet to each of the store managers; for example, send the Texas sheet to Texas, etc. Their copies include the spreadsheet with static data of their branch and formulas that operate on their spreadsheet only.
The macro function will provide the formulas (grand totals of all stores) for the Master spreadsheet only.
Once the individual stores get back to you with their spreadsheets, you will copy the individual sheets from the 8 stores manually to your master spreadsheet.

Automate data collection

Program Macro1

Access your dedicated database folder on your PC and open your MasterDB.xlsx spreadsheet (example: C:\Users\Exgap\Documents\Database\masterDB.xlsm)
Open one of the new store spreadsheets (example: (example: C:\Users\Exgap\Documents\Database\MiamiDB.xlsm)
Move your mouse cursor back to the MasterDB.xlsx to be the active sheet.
Select the View tab>Macros>Record Macro
You can set the macro name field as "MiamiDB_Macro".
Enter a shortcut key in the Shortcut_key field box (Optional)
In the "Store Macro", select the dropdown arrow and select "Personal Macro Workbook" from the list, then click OK.

Done, at this point, you are recording every step you make in the macro. Follow the next directions, and use your mouse to navigate through the spreadsheet.

Execute Macro1

Move back to the MiamiDB spreadsheet, right-click the Miami worksheet tab and in the popup menu, select "Move or Copy"
In the "Move or Copy" dialog box, check the "Copy" box.
In the "Move Selected Sheets" dialog box, select the "To Book" dropdown arrow and choose "MasterDB.xlsx"
In the "Before the Sheet" box, select the first spreadsheet on the list which in our case it's the “Master” spreadsheet then click OK. That lets Excel copy the sheet to the MasterDB under a new worksheet called “Miami2.”
Right-click the tab of the original Miami spreadsheet and pick Delete from the popup menu.
In order to truly replace the new Miami sheet that the Miami manager sent to you, rename the new sheet by deleting "2" (Miami2 > Miami)
Position the new Miami sheet where the original sheet was located ( between Las Vegas and New Orleans tabs)
Go to View tab > Macro > Stop Recording
Save the MasterDB.xlsm and MiamiDB.xlsm files
Go back to MasterDB and run the macro with the custom shortcut or go to View tab > Macro > View Macros > Run
If the macro function works as we wanted, repeat the process for the rest of the 7 spreadsheets.

Automate Data Sorting

Programming Macro2

Access your dedicated database folder on your PC and open your MasterDB.xlsx spreadsheet (example: C:\Users\Exgap\Documents\Database\masterDB.xlsm)
Select the View tab>Macros>Record Macro.
You can set the macro name field as "Sorting_Macro".
Enter a shortcut key in the Shortcut_key field box (Optional)
In the Store Macro, select the dropdown arrow and select "Personal Macro Workbook" from the list, then click OK.

Executing Macro2

Select a worksheet from the MasterDB
Press Ctrl+ Home in order to move the cursor to A1 cell.
Press the Down arrow key three times.
Press Shift+Ctrl+Down + Right in order to highlight the table content
Navigate to Data > Sort and from the Column dropdown menu choose "SKU Number", from the Sort on dropdown menu pick "Cell Values" and from the Order dropdown menu choose "A to Z". Press OK
Press Ctrl+ Home again
Repeat the process for the next 8 spreadsheets
Once you finished the process, pick the Master spreadsheet tab and press Ctrl+ Home.
Stop Recording the macro by navigating with your courser to View > Macro > Stop Recording
Save the MasterDB. (Ctrl+S)
At this point, you can go ahead and test out the macro function by using the custom shortcut OR by going to View tab > Macro > View Macro > choose "PERSONAL.XLSB!Sorting_Macro" and press "Run"

Automate formulas

Programming Macro3

Formulas for individual stores are already in position, we entered them back in the Prep Work section above. The following formulas will be for the Master Spreadsheet alone which will calculate the combined sum of all store spreadsheets.
The programming part of this macro is going to be the same as for Macro1 and Macro2, and it follows:

Access your dedicated database folder on your PC and open your MasterDB.xlsx spreadsheet (example: C:\Users\Exgap\Documents\Database\masterDB.xlsx)
Select the View tab>Macros>Record Macro.
You can set the macro name field as "Formula_Macro".
Enter a shortcut key in the Shortcut_key field box (Optional)
In the Store Macro, select the dropdown arrow and select "Personal Macro Workbook" from the list, then click OK.

Executing Macro3

Pick the Master spreadsheet tab
Press Ctrl+Home
Press Down-Right-Right
=SUM(Philadelphia:Seattle!C2) and press Enter key (This formula has to be located in the Cell C2, while the tabs name Philadelphia and Seattle represents the first and last spreadsheet tab in the MasterDB workbook excluding the Master Workbook which has to positioned before Philadelphia)
Press Right-Right-Shift+Right-Right to highlight cell values and hit Enter. (This way Excel will enter the rest of the formulas for you from E2 to H2 cells)
While the cells are highlighted, press Shift+Ctrl+4 in order to format the cells as Currency values.
You can go ahead and test out the macro function by using the custom shortcut OR by going to View tab > Macro > View Macro > choose "PERSONAL.XLSB!Formula_Macro" and press "Run"