قالب وردپرس درنا توس
Home / Tips and Tricks / How to create Excel macros & automate your spreadsheets

How to create Excel macros & automate your spreadsheets



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
    , B19, C20, etc.) in the macro . Spreadsheets are dynamic, which means that they constantly change, which means that the cell addresses change.
  • 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:

  1. Collect and combine the data from its 12 stores into one workbook in a three-dimensional main sheet.
  2. Organize and sort the data. [19659006] 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.

download

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.


Source link