Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

macros

ResolvedVersion 2013

Olga has attended:
Excel Advanced course

MAcros

Could I please run through process again to make sure all works

RE: MAcros

Hi Olga,

No problem, of course you can.

1. Choose Record Macro on the View Tab
The Record Macro dialog box appears.

2. Type a name for the macro in the Macro Name
The first character of the macro name must be a letter, and the name cannot contain spaces or cell references. Macro names are not case-sensitive.

3. (Optional) Assign a Shortcut Key.
If you select a shortcut key already used in Excel like Ctrl+ S, the macro shortcut key overrides the Excel shortcut key while the workbook that contains the macro is open. Ctrl-Shift-Y was the one we used

4. From the Store Macro In drop-down list, select where you want to store the macro:
• This Workbook: Save the macro in the current workbook file.
• New Workbook: Create macros that you can run in any new workbooks created during the current Excel session.
• Personal Macro Workbook: Choose this option if you want the macro to be available whenever you use Excel, regardless of which worksheet you're using.

5. Type a description of the macro in the Description text box.

6. Click OK.
The Record Macro option on the Developer tab changes to Stop Recording.

7. Perform the actions you want to record.
Excel records you exactly. If you want it to record your actions relative to where you are clicked. When I click I want it to highlight the row I’m on, for example, in the Macros drop down on the view tab click use relative references.

8. Choose Stop Recording on the view tab.
The macro recorder stops recording keystrokes and the macro is complete.

9. To run the Macro
Use the Shortcut or head up to the View Tab and click on the Macro Box. When this opens choose RUN. If you saved it to your Workbook, then you need to have that File open to “borrow” the macro.

10. Problems when running
Sometimes the Macro will not run correctly. This is generally because you created a unique item, like Pivot Table 1 or Chart 1. If you run the Macro, it remembers that Excel has already created a Pivot Table 1 in this session, so it wont create another one.
If you go to the View Tab/ Macro and click on Step into – you can edited the code. If you run one – debug will also bring you here.
You will see the code line it is having problems with highlighted in Yellow.
If you change the Chart 1 to Chart 2 and save
You should find the Macro works again.

Let me know if that helps, or if there's anything else I can help with.

Kind regards

Richard Bailey
Microsoft Certified Trainer

Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Fri 18 Mar 2016: Automatically marked as resolved.

Excel tip:

Hiding Formulae in the Formula Bar

It is possible to protect the contents of a cell reference(s) from amendment by applying cell protection. The contents of the cell reference can also be stopped from displaying in the formula bar.

Step 1: Select Format > Cells > Protection.

Step 2: Tick Hidden option. Ensure Locked is ticked

Step 3: Select Tools > Protection > Protect Sheet

(Ensure "Protect worksheet and contents of locked cells" is ticked)


View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.