Excel macro & # 39; s are just like mini-programs that perform repetitive tasks, saving you a lot of time and typing. For example, it costs Excel less than a tenth of a second to calculate an entire, massive spreadsheet. It is the manual actions that slow you down. That's why you need macros & # 39; s to combine all these tasks into a single one-second transaction.
Excel macro & # 39; s: tips to get started
We'll show you how to write your first macro. Once you see how easy it is to automate tasks using macros, you will never come back.
First some tips for preparing your data for macro & # 39; s:
- Always start your macro in the starting position (use the key combination Ctrl + Home to get there quickly).
- Use the directional keys to navigate: Up, Down, Right, Left, End, Start, etc., and shortcut keys to speed up movement.
- Keep your macro & # 39; s small and focused on specific tasks. This is best for testing and editing (if necessary). You can always combine these mini-macros later into one BIG macro once they are perfected.
- Macros require "relative" cell addresses, which means that you "designate" the cells instead of the actual (or "absolute") hard code cell address (such as A1
- Fixed values and static information such as names, addresses, ID numbers, etc. are generally entered in advance and are not really part of your macro. Because this data rarely changes (and if this is the case, it is only to add or remove a new record), it is almost impossible to include this function in a macro.
- First manage your data: add, edit or delete records, then enter the updated values. Then you can run your macro.
Why starting with mini-macro & # 39; s is easier
For this example, we have a store owner who has expanded her territory from a single store to a dozen in 12 different major cities. Now, the CEO, she has been managing her own books for years, which was not an easy task for a single store, and now she has 12. She needs to collect and aggregate data from each store to monitor the health of her entire business.
We have created a few mini-macros to perform the following tasks:
- Collect and combine the data from its 12 stores into one workbook in a three-dimensional main sheet.
- Organize and sort the data.  Enter the formulas that calculate the combined data.
After the mini-macros & # 39; s are recorded, tested and perfected, we can merge them into one large macro or have them as mini-macros & # 39; s. Either way, save the mini & # 39; s because it is much easier and more efficient to edit and recombine the smaller & # 39; s smaller than trying to browse through a long, detailed macro to find errors .
We have given an example workbook for the above scenario so that you can follow our instructions. You can of course also create your own spreadsheet.
This sample Excel workbook helps you practice and create macros. JD Sartain
Preparation: the main spreadsheet
If you are completely rebuilding your spreadsheets, start with the main spreadsheet. Enter the date formula in A1 and the store location in B1. See the screenshot below.
Enter this date formula in cell A1: = Today () . Now this cell always displays today's date. Be sure; that your store location (branch name and number) is entered in B1.
2. Leave row 2 empty. After the static data and the first dynamic data have been entered, we use row 2 for the totals. This may seem like a strange habit, but for macro spreadsheets this is the best way because this row is stationary and always visible.
3. Then enter the field names (and / or other field-specific information) in row 3 (for example, from A3 to J3, or how many fields your spreadsheet needs).
Tip: You can wrap the information in the individual cells if the data is long. For example, you can place the store contact information in one cell and wrap the rules. Press Alt + Enter to insert extra lines in the cells.
4. Then enter the static data in column A. That is the record information in your spreadsheet that rarely changes. If your company uses product numbers or ID codes that are unique because there is only one code per product, enter them in column A from row 4 (do not go to row 5). Other static data fields can be the product description, the product price, the VAT percentage, etc.
Do not skip or leave any rows blank for column A. Each row must contain the data of the unique field, if not a product code, then another unique identifier. We do this for two reasons:
- Column A is the main navigation column. The macro moves and navigates through the spreadsheet based on the Home (A1) position and column A. The macro fails if you ignore this rule because empty rows disrupt the directional key actions.
- If you decide to have multiple / relational tables later for Pivot Reports, you must have a unique key field to connect the related tables. View our Excel pivot table tutorial for more information.
5. Normally the product description is in column B, the quantity sold in column C, product price in column D, extensive costs in E, discounts in F, sales tax in G and totals in H. The column totals are at the top of row 2, you know yet? Format the column widths based on the length of the field names and adjust the row height to 20 on all rows. Change the top / bottom alignment to Center, select the justification that you prefer (left, right, center) and then format the "styles" of the spreadsheet to your preference.
6. After the main database is set up, you may not move anything anymore. If you need to add fields, use the Insert Column command. For example, if you want to add a second sales tax, place your cursor anywhere on column H (Totals) and click the tab: Home> Insert> Insert Sheet Columns . The new column falls into the new H column and the Totals column goes to I. This process does not affect the macro.
7. The same process applies to rows. Normally I would warn you to insert rows "within" the active database area. For example, if the formula says = SUM (B3: B20) and you insert or use a row outside the range of the formula such as B21, the data from the new record is not included in the formula and is therefore not calculated.
8. Now we will set that formula range. Enter the following formulas in row 2 (this is a one-time task):
C2: = SUM (C4: C500)
E2: = SUM (E4: E500)
F2: = SUM (F4: F500)
G2: = SUM (G4: G500)
H2: = SUM (H4 : H500)
Then enter the following formulas in these columns (also a one-off event):
E4: = SUM (C4 * D4) and then copy from E4 to E5 : E500
F4: = SUM (E4 * 10%) the current discount percentage in your store, then copy from F4 to F5: E500
G4: = SUM (E4-F4 ) * 6.25 where 6.25 is the sales tax in your area, then copy from G4 to G5: G500
H4: = SUM (E4-F4 + G4) and then copy from H4 to H5 : E500
Now that you have all the spreadsheet formulas in place, all you have to do is enter the quantity (column C) for every computer sold (daily, weekly or monthly). If the prices change, enter the new prices in column D. The rest of this database consists of formulas or static information.
9. As shown above, with "macro" spreadsheets you set the formula range to many rows beyond the last record, so you can just add new records at the end and you don't have to worry about adjusting the range. Because the macro sorts the database, the new records are moved to the correct position. The spreadsheet data in our example ends in row 210. The formula range extends to row 500, so it is safe to add the next new record to row 211.
10. After the spreadsheet has been defined and set with the structure, the static data in place and the correct formulas, create 12 copies in worksheets 2 through 13. Edit the tabs below to identify the individual stores. Change the name of the sheet1 tab to Master, because this is your main database file.
11. Change the location data in row 1 to identify the store information (corresponding to the store on the tab) on all 12 spreadsheets. Then e-mail an electronic copy of the spreadsheet from each store to each of the store managers; For example, send the Boston sheet to Boston, the Dallas sheet to Dallas, etc.
Their copies contain the spreadsheet formulas that work on their individual spreadsheets (but not the formulas of the combined spreadsheets in the workbook).
12. The macro offers the formulas for the master. The master is the spreadsheet for the combined totals of all stores. If you are the one who collects all data and executes the main & # 39; s AND you also manage an individual store, you must use one of the 12 sheets that you have copied for your store. The master is only for the grand totals.
13. As soon as the branches email their individual spreadsheets, it is safer to simply copy the individual sheets manually from the 12 workbooks of the stores.
Now we are ready to program a macro! Just click on the next page.