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

How to create Excel macros and automate your spreadsheets



Excel macros are like mini-programs that perform repetitive tasks, saving you time and typing. For example, Excel takes less than a tenth of a second to compute a full, massive spreadsheet. It is the manual processes that slow you down. That's why you need macros to combine all these tasks in a single one-second transaction.

Excel Macros: Getting Started

We'll show you how to write your first macro. Once you realize how easy it is to automate tasks with macros, you'll never come back.

Here are some tips for preparing your data for macros:

  • Always start your macro at its starting position (use the keyboard shortcut Ctrl + Pos to get there quickly).
  • Use the directional buttons to navigate: Up, Down, Right, Left, End, Home, etc., and shortcuts to speed up the movement.
  • Keep your macros small and focus on specific tasks. This is best for testing and editing (if necessary). You can always combine these mini-macros into a BIG macro when they are perfected.
  • Macros require "relative" cell addresses ") Cell address (such as A1
    , B19, C20, etc.) in the macro. Spreadsheets are dynamic, that is, they are constantly changing, that is, the cell addresses are changing.
  • Fixed values ​​and static information such as names, addresses, ID numbers, etc. are generally pre-entered and are not really part of your macro. Since this data seldom changes (and if so, you only need to add or remove a new record), it is almost impossible to include this function in a macro.
  • Manage your data first: then add, edit or delete records Enter the updated values. Then you can run your macro.

Why It's Easier to Start with Mini-Macros

For this example, we have a shopkeeper who has expanded his territory from a single store to a dozen in 12 different cities. Now she is the managing director. She's been running her own books for years, which was not an easy task for a single store, and now she has twelve. She needs to collect and merge data from each store to monitor the health of her entire business

We've created a few mini-macros to perform the following tasks:

  1. Collect and combine the data from their 12 stores into one Workbook in a three-dimensional master table.
  2. Organize and sort the data.
  3. 19659006] Enter the formulas that compute the combined data.

Once the mini-macros have been recorded, tested and perfected, you can merge them into a large macro or leave them as mini-macros. Be sure to keep the mini-macros, because it's much easier and more efficient to manipulate and recombine the smaller macros than to look for a long, detailed macro to find bugs.

We have provided an example workbook for the above scenario so that you can follow with our guide. Of course, you can also create your own table.

download

In this Excel workbook you can practice creating and using macros. JD Sartain

Preparatory Work: The Master Spreadsheet

When you create your spreadsheets from scratch, you start with the master spreadsheet. Enter the date formula in A1 and the location in B1. See picture below.


Source link