How to create Excel macros and automate your spreadsheets - belangermopine

Excel macros are like mini-programs that perform iterative tasks, saving you a fortune of time and typing. For object lesson, it takes Excel less than ten percent of a second to calculate an entire, massive spreadsheet. It's the manual trading operations that slow down you descending. That's why you need macros to combine all of these chores into a I one-second transaction.

Excel macros: Tips for getting started

We'atomic number 75 going to show you how to write your first macro. Once you see how slow it is to automate tasks using macros, you'll never come back.

First, some tips connected how to prepare your data for macros:

• Ever Begin your macro at the Abode position (use the key combinationCtrl+ Home to get there quickly).
• Use the directional keys to navigate: Up, Push down, Right, Remaining, End, Home, etc., and shortcut keys to hasten movement.
• Keep your macros lowercase and focussed along specific tasks. This is best for testing and editing (if needed). You can always combine these mini-macros into one BIG macro later in one case they're perfected.
• Macros require "relative" cellular phone addresses, which means you "point" to the cells rather than hardcode the actual (or "absolute") cell dea (such as A1, B19, C20, etc.) in the macro. Spreadsheets are dynamic, which way they constantly change, which means the cell addresses change.
• Fixed values and static information such as names, addresses, ID numbers racket, etc. are generally entered in advance and not really part of your macro. Because this data rarely changes (and if it does, IT's just to add or remove a recent record), it's almost impossible to include this function in a macro instruction.
• Manage your data first: Add, edit, or delete records, then enter the updated values. Then you john run your macro.

Wherefore protrusive with mini-macros is easier

For this example, we have a store owner who has expanded her dominio from a single fund to a dozen in 12 contrary senior cities. Now the CEO, she's been managing her own books for years, which wasn't an easy task for a separate store, and now she has 12. She has to collect data from for each one store and merge it to monitor the health of her entire company.

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

1. Collect and trust the information from her 12 stores into one workbook in a Master three-dimensional spreadsheet.
2. Devise and sort the data.
3. Enter the formulas that calculate the combined data.

Erst the mini-macros are tape-recorded, tested, and perfected, we dismiss merge them into one big large or lead them as mini-macros. Either way, keep the mini-macros, because information technology's very much easier and many efficient to edit out the smaller macros and re-combine them, than try to step through a long, detailed macro to come up errors.

We've provided a sample workbook for the above scenario so you can follow along with our how-to. Feel free to make up your own spreadsheet too, of course.

download

This try Stand out workbook wish help you practice creating and victimization macros. JD Sartain

Prep work: The Master spreadsheet

If you're edifice your spreadsheets from the ground up, beginning with the Headmaster spreadsheet. Enter the date chemical formula in A1 and the store location in B1. See screen shot below.

Enter this engagement formula in cell A1: =Nowadays(). Forthwith this cell always displays today's date.  Be sure; nonetheless, that your store position (branch diagnose and number) are entered in B1.

2. Leave row 2 blank. Once the stable data and initial dynamic data are entered, we'll utilize row 2 for the totals. This might seem like a strange custom, but for macro spreadsheets, it's the Sunday-go-to-meeting path because this quarrel is stationary and always visible.

3. Next, enter the field name calling (and/or some other field-particular data) in course 3 (e.g., from A3 through J3, or however many fields your spreadsheet requires).

Tip: You rear text-wrap the info in the individual cells if the data is prolonged. For example, you tin put the store contact information all in one cell and wrap the lines. Crusade Alt+ Enter to tuck extra lines in the cells.

4. Next, enrol the stable information in column A. That is the record information in your spreadsheet that rarely changes. If your business uses product numbers or ID codes, which are unique because there is only one inscribe per product, enter those in column A beginning happening row 4 (preceptor't skip to words 5). Former static information fields power include the Product Description, the Product Monetary value, gross sales assess percentage, etc.

Do non skip rows or leave any rows dummy for column A. Every row must contain the unique field's data—if not a product cypher, then other unique identifier. We do this for two reasons:

• Column A is the main navigational column. The macro moves and navigates through the spreadsheet based along the Home (A1) position and column A. The macro wish fail if you ignore this reign, because blank rows disrupt the actions of the directional keys.
• If you decide to create multiple/relational tables later for Pin Reports, you essential have a unique, key field to connect the allied tables. Check kayoed our Excel pivot tables tutorial for more information.

JD Sartain / IDG Worldwide

Ramp up the Master key spreadsheet inaugural.

5. Normally, the Production Description resides in column B, the Quantity Sold-out in column C, Product Price in column D, Extended Price in E, Discounts in F, Sales Tax in G, and Totals in H. The column totals are across the top on row 2, think of? Format the column widths settled on the length of the field of study names, and adjust the course acme to 20 on each rows. Change the Top/Butt alignment to Center, select the justification you choose (left, right, center), and then initialize the spreadsheet "styles" to your preference.

6. Once the master database is constituted, do not move anything. If you need to add Fields, use theInsert Tower command. For example, if you cherished to add a 2nd gross revenue taxation, position your cursor anywhere on column H (Totals) and come home the tab key: Home > Insert > Insert Sheet Columns. The new editorial drops in to become the new H column, and the Totals column moves over to I. This outgrowth does not affect the macro.

7. The same process applies to rows. Unremarkably I would caution you to insert rows "inside" the active database expanse. For example, if the formula says =SUM(B3:B20) and you stick in or use a row outside of the chemical formula's range like B21, the new record's data is not enclosed in the formula and thence, does non calculate.

8. Now we'll set up that pattern range. Enter the next formulas on row 2 (this is a one-sentence task):

C2: =SUM(C4:C500)

E2: =SUM(E4:E500)

F2: =SUM(F4:F500)

G2: =Summate(G4:G500)

H2: =SUM(H4:H500)

Next, move in the following formulas in these columns (also a peerless-time event):

E4: =SUM(C4*D4), then copy from E4 knock down to E5:E500

F4: =Marrow(E4*10%), the current dismiss percentage in your fund, then written matter from F4 down to F5:E500

G4: =SUM(E4-F4)*6.25, where 6.25 is the sales tax in your area, and so simulate from G4 down to G5:G500

H4: =SUM(E4-F4+G4), then copy from H4 weak 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 each computing machine sold (each day, weekly, or monthly). If the prices change, enter the untested prices in editorial D. The rest of this database is all formulas or static information.

JD Sartain / IDG Worldwide

Enter the formulas to figure the columns and rows.

9. Arsenic seen above, with "macro" spreadsheets, you set the formula range to be many rows beyond the last record, so you can just add new records at the ending and non worry about adjusting the orbit. Because the big sorts the database, the new records are settled to the proper put up. The spreadsheet data in our example ends happening run-in 210. The formula kitchen range extends out to row 500, and then IT's safe to add the next new record on row 211.

10. Once the spreadsheet is defined and set up with the structure, static data in put off, and correct formulas, make 12 copies in worksheets 2 through 13. Edit the tabs on the bottom to distinguish the individual stores. Change the list of the sheet1 tab to Get over, because this is your master database single file.

11. Change the location information happening course 1 to place the storehouse entropy (that matches the store along the tab) on all 12 spreadsheets. Next, email an physical science copy of for each one branches' spreadsheet to from each one of the store managers; for example, post the Boston sheet to Boston, the Dallas sheet to Dallas, etc.

Their copies include the spreadsheet formulas that work on their individual spreadsheets (but not the formulas of the cooperative spreadsheets in the workbook).

12. The macro provides the formulas for the Master. The Master is the spreadsheet for the combined totals of totally stores. If you are the same who collates all the data and executes the Master macros AND you too manage an one-on-one store,  you moldiness use single of the 12 sheets you traced for your store. The Skipper is for the grand totals only.

13. Once the branches netmail their unshared spreadsheets, information technology's safer to just copy the individual sheets from the 12 stores' workbooks manually.

JD Sartain / IDG Worldwide

Copy the Master spreadsheet 12 multiplication, past name the tabs.

In real time we're set up to program a macro! Just click to the next Page.

Programming macros

Macro1: Collect and combine data

1. Approach your database folder and admissive your spreadsheet titled MasterDB.xlsx

2. Unprotected one of the new stock spreadsheets, so much as the one titled BostonDB.xlsx

3. Move your pointer back to the MasterDB so it's the participating sheet.

4. Select the Developer tab and click Record Macro operating room press ALT+ L+ R. The Macro Name field says Macro1, and that's a good identify.

5. Accede a shortcut key (if you like) in the Shortcut_key field box (enter the letter M) (you can produce a button on the Ribbon menu afterwards).

JD Sartain / IDG Worldwide

Record macro panel, macro shortcut_key

6. In the Stack away Macro In flying field box, click the down arrow and select Personal Macro Workbook from the heel, past chink OK.

Instantly you are recording the macro.

Keep abreast the instructions below, precisely, and use your shiner to navigate around the spreadsheet. Please note that phrases wrong quadrate brackets are tips, notes, and explanations of the instructions. Do not include these phrases surgery anything they aver in your macro.

1. Move back to the BostonDB spreadsheet, then right-tick the Boston pill. In the popup menu, select Move or Copy …

2. In the Make a motion or Copy dialog, fit the box that says Replicate.

3. In the Move Selected Sheets dialog, clink the down arrow beside the To Books field box seat.

4. Select "MasterDB.xlsx" from the list.

5. In the second dialogue: Before the Sheet, select the first spreadsheet on the list called "Master," then click OK.

8. Excel copies the flat solid and relocates your pointer to the MasterDB. Notice the modern tab that says "Boston2." Verify that the info in cell A1 shows the store number followed by a modern date stamp (9/29/18 in this representative). If yes, you're good to go off.

9. Rightmost-click the yellow journalism of the original Boston spreadsheet and quality Delete from the popup menu.

10. Surpass warns in a dialog box: You Can't Undo . . . Delete or Cancel? If you're certain you want to remove it click Erase.Why? Because you want to replace information technology with the NEW Boston weather sheet that the Boston manager conveyed to you.

11. Move the Boston2 tabloid between the Master and Chicago tabs. If you keep the '2' from Boston2, it volition be easier to quickly recognize which sheets own been updated each month.

13. Click Ctrl+ Home to relocate pointer to mobile phone A1 and re-enter this date formula: =Now(), (if this expression is missing), and so press the Enter key

JD Sartain / IDG Worldwide

Record the macro, combine the data, delete duplicate sheets

Perform the Macro

1. Select the Developer tab key again and click Stop Recording or press ALT+ T+ M+ R.

2. Save the Master file, then keep the BostonDB file.

3. Go back to the MasterDB spreadsheet and run the macro: Press Ctrl+ M.

Government note: Call up that the addition sign means a "simultaneous" combination keystroke; that is, Ctrl+ Shift+ J means: Press and hold down the Ctrl and Shift keys with your leftish hand, then press the J key with your mighty turn over, then release all three keys at the same time. The dash (or hyphen) means a "consecutive" combination keystroke, such as End- Down, which means pressur the Goal winder and release, then press the Down pointer and expiration. These are NOT interchangeable, so watch the signs.

4. If the macro deeds as expected, repeat this process again for each of the unexpended 11 spreadsheets, and then run the macros, save the files, and exit all spreadsheets except the Master.

NOTE: The only available crosscut keys are Ctrl+ M (which you give already used), Ctrl+ Shift+ M, Ctrl+ J, and Ctrl+ Shimmy+ J. Because shortcut keys are in short supply and the theatrical role combinations don't build any analytical feel at any rate, the best solution for your mini macros are large buttons on the Ribbon menu with name calling that make sense, such as Boston for the Boston macro and Dallas for the Dallas big. Feel out this former Excel macros how-to, where there's a section with detailed instructions happening how to create, name, and use macros.

Macro2: Direct and sort data

This one is easy, but with so many a spreadsheets, it can be a daunting task if you coif it manually. Excel really provides a way to qualify all your spreadsheets at once, but this task is unreliable when sorting.

Follow the Record Macro instructions (4, 5, 6 under Macro1 above) to create this next macro. Name the big Macro2 and use Ctrl+ Shift- M for the crosscut (you can make up a button on the Ribbon fare later). This macro affects all the spreadsheets in the MasterDB, so ensure this register is heart-to-heart and activist.

1. Press Ctrl+ Household [to move cursor to A1].

2. Press the Pile arrow key deuce-ac multiplication.

3. Press Shift- End- Thrown- End- Right [Hold low-spirited the Shift, conjur the End samara and spill, press the Drink down Arrow and release, contrac the End key and release, press the Right arrow and release].

4. Choose Data > Sort. In the Screen dialog, pick out Model Number from the drop-down list in the Sort By field box, then choose Values from the Sort Along field boxful, and then choose A-Z from the Sort Order field box, and click Sooner State.

JD Sartain / IDG Worldwide

Macro instruction sorts the spreadsheet aside Model Number

5. Press Ctrl+ Home.

6. Click the next tab at the bottom to memory access the next spreadsheet (i.e., Chicago after Boston), and retell all steps above: 1-6, and then continue with the following instruction manual below. Remember, the macro is recording through all these steps.

7. Click the Master spreadsheet tab, press Ctrl+ Home.

8. Select the Developer tab (from Ribbon menu) and click Stop Transcription or press Altitude+ T+ M+ R.

9. Save the Master file, MasterDB.

10. With cursor still in MasterDB spreadsheet, run the macro: Press Ctrl+ Shift+ M.

Macro3: Enter formulas

The formulas for the individual stores' spreadsheets are already in put on. You entered those back in step #9 of the Prep Make section above. These formulas are for the Skipper spreadsheet, which calculates all the others and combines the grand totals into one "master" sheet. We use a macro for this process rather than doing it manually 12 times.

Follow the Record Macro instruction instructions (4, 5, 6 low-level Macro1 above) to create this incoming macro. Name the macro Macro3 and use Ctrl+ J for the shortcut (you can create a button on the Ribbon menu later). This macro affects whol the spreadsheets in the MasterDB, so ensure this file cabinet is agape and active.

1. Contrac Ctrl+ House [to move cursor to A1].

2. Press Downfield- Right- Right

3. =Aggregate(Boston:Denver!C2) Enter [Enter this formula in cadre C2, where the tabs named Hub of the Universe and Denver represent the first and last spreadsheet check names in your workbook. This is excluding the Master, course, because you are calculating all the values in cell C2 from the first tab Capital of Massachusetts direct the antepenultimate tab Denver and entering the totals in cell C2 of the Master. Then Enter key is pressed).

4. Rising pointer, Ctrl+ C [Moves pointer back up to cell C2 and copies this formula]

5. Right- Right- Shift- Mighty- Right- Right Enter [moves cursor to the right twice and Newmarket on electric cell E2, press the Shift Key and hold down while moving to the aright threefold, which highlights cells E2 thru H2, and then press the Enter key].

JD Sartain / IDG Comprehensive

Enter+ count formulas in Master for multiple sheets

6. [Piece these cells are stillness highlighted, pressure] Shift+ Ctrl+ 4

7. ALT+ T+ M+ R [Conjur these keys at the same time operating theatre select the Developer tab and chatter Stop Transcription].

Pull through, copy, and distribute

1. Ctrl+ Home

2. Economize the Master file in, MasterDB.

3. Send copies of the MasterDB to all stack away managers.

Debugging your macros

Debugging macros in Excel is really easier than you might think. Printing and reading through with your macros will help you check Sir Thomas More about how Surpass's macro language (Visual Basic) whole works. You don't have to live a coder to debug macros, and the more you have it off, the easier it gets.

1. First, create a simple macro instruction so you can see what the various instructions look like in Visual Basic.

2. Select the Developer tab, and then sink in the Record macro picture/button.

3. Enter the keystrokes that perform the task you are automating, then click the Macro Period icon/button.

4. To catch your large, chatter the Macros icon/button or press Alt+ F8.

5. The Macros dialog appears, displaying altogether the macros blessed to the "Each Open Workbooks" country.

6. Locate your macro, click to select, then click the Step Into button.

7. Your macro code appears in the Visual Basic macro debug window

The first line says: Sub macroname()

Sub means Subroutine, which alerts Surpass that a macro follows. "Sub" begins the macro and "Finish Sub" ends it. The side by side credit line shows the macro name (I named information technology xdebug().

Sub xdebug()

The next section says this:

Select Range C3. Then enter formula =Center(RC[-1]*10%) in this ActiveCell

The following commands are similar:

Select Range D3. So enter formula =SUM(RC[-2]*32%) therein ActiveCell

Prize Mountain range E3. Then enter formula =SUM(RC[-3]:RC[-1]) in this ActiveCell

Next, it says:

Select Range C3 through E3 and Re-create

Select Range C4 through E12 and Glue

Application.CutCopyMode = False [this agency clear the clipboard]

So information technology continues with:

Select Range E4

Select the End key, then wane one row

Select Range E13. So enter recipe =SUM(R[-10]C:R[-1]C) in that ActiveCell

These next lines are text alignment commands:

Select Range E13

With Selection (format as follows)

.HorizontalAlignment = xlGeneral

.VerticalAlignment = xlCenter

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

With Choice (format as follows)

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

Close Sub

JD Sartain / IDG

How to debug a big

Just the likes of HTML and most other languages, certain statements (or commands) that suffer a beginning operating theater surface statement moldiness also have an ending statement.

To make changes, just type them in, then click Alt+ Q to return to the spreadsheet. Excel displays a dialog windowpane that says: This command volition stop the Debugger. Just cluck the Oklahoma button and the macro instruction disappears.

If you make an editing misapprehension, exit, then run the macro instruction, Excel displays this computer error content: Compile Erroneous belief: Sentence structure Error. Compose means "convert the code" to some format is compulsory to make this computer program/large function. Sentence structure evenhanded means "sentence," OR basically, you suffer an error in uncomparable of your command sentences.

Click the OK button, and Excel displays the large cipher on the screen. The location of the error is in red, so you give the sack go instantly to the misidentify and fix it. Once repaired, the red textual matter leave turn black. Then save it over again; Press Alt+ Q, run it again, and if the fix was correct, the macro works.

JD Sartain / IDG

Understanding macro error messages

Source: https://www.pcworld.com/article/402678/how-to-create-excel-macros.html

Posted by: belangermopine.blogspot.com

Share this post