Contains paid promotion

Advertisement

How to use in just a few steps
Macros in Excel 2016

Almost everyone has heard of the term “macro” in the context of Excel, and perhaps has shied away from it because it sounds like a rather complicated affair for which you have to deal with Excel programming.

However, some people have never encountered this function before, since it first has to be activated in the developer tools.

Makros in Excel 2016 erstellen

But do not worry, because neither the menu item Developer Tools, nor the creation of macros with which you can record workflows, and later with keyboard shortcuts, or play button again must be a book with seven seals.

How it is done in Excel 2016 you will learn in our article.

Anzeige
SanDisk Ultra 128GB microSDXC Speicherkarte + Adapter bis zu 100 MB/Sek, Class 10, U1, A1, FFP
List Price: € 52.99 You Save: € 23.00 (43%) Prime Price: € 29.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. October 2018 um 7:31 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

How to use in just a few steps Macros in Excel 2016

Almost everyone has heard of the term “macro” in the context of Excel, and perhaps has shied away from it because it sounds like a rather complicated affair for which you have to deal with Excel programming.

However, some people have never encountered this function before, since it first has to be activated in the developer tools.

Makros in Excel 2016 erstellen

But do not worry, because neither the menu item Developer Tools, nor the creation of macros with which you can record workflows, and later with keyboard shortcuts, or play button again must be a book with seven seals.

How it is done in Excel 2016 you will learn in our article.

Anzeige
SanDisk Ultra 128GB microSDXC Speicherkarte + Adapter bis zu 100 MB/Sek, Class 10, U1, A1, FFP
List Price: € 52.99 You Save: € 23.00 (43%) Prime Price: € 29.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. October 2018 um 7:31 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

1. Enable developer tools in Excel 2016.

1. Enable developer tools in Excel 2016.

In our little example we have created 2 dynamic tables with which we want to work.

The goal will be to transfer the data entered in the first table into the second table by means of a button, and then continue to update it.

If you have not already done so, you will first need to unlock the developer tools to get the function of the macros. To enable Developer Tools, go to the tab:

File” – “Options” – “Customize Ribbon” and put the appropriate hook there.

Then, by default, you enabled your new additional Developer Tools tab in Excel.

See picture: (click to enlarge)

Developer Tools in Excel 2016
Advertisement

In our little example we have created 2 dynamic tables with which we want to work.

The goal will be to transfer the data entered in the first table into the second table by means of a button, and then continue to update it.

If you have not already done so, you will first need to unlock the developer tools to get the function of the macros. To enable Developer Tools, go to the tab:

File” – “Options” – “Customize Ribbon” and put the appropriate hook there.

Then, by default, you enabled your new additional Developer Tools tab in Excel.

See picture:

Developer Tools in Excel 2016
Advertisement

2. Record macro in Excel.

2. Record macro in Excel.

As already said, the goal is to transfer and update data from one table to another via a button.

For this we have to think carefully about our work steps.

We will copy the entries in the first table step by step during the recording, insert them into the second table accordingly, and finally insert a new line to make room for the next entry.

Because we want to create an updating table.

First click on “Record Macro” in the tab “Developer Tools“.

In the next window, give the macro a name, and make sure that there are no spaces. So if you use two or more words (as in our example), you must separate them with a band or underscore.

After clicking on “OK” the recording starts, and we execute exactly the work steps, which are to be executed automatically later by pressing a button.

Schluesselnotdienst Aktion - kautionsfrei.de
Anzeige

When we have completed all the steps, we finish the recording in the register:

Developer Tools” – “Stop Recording

See picture: (click to enlarge)

Record macro in Excel 2016
Create a macro in Excel
Makro in Excel 2016 erstellen
Insert empty line in dynamic table
Stop macro recording in Excel

As already said, the goal is to transfer and update data from one table to another via a button.

For this we have to think carefully about our work steps.

We will copy the entries in the first table step by step during the recording, insert them into the second table accordingly, and finally insert a new line to make room for the next entry.

Because we want to create an updating table.

First click on “Record Macro” in the tab “Developer Tools“.

In the next window, give the macro a name, and make sure that there are no spaces. So if you use two or more words (as in our example), you must separate them with a band or underscore.

After clicking on “OK” the recording starts, and we execute exactly the work steps, which are to be executed automatically later by pressing a button.

Schluesselnotdienst Aktion - kautionsfrei.de
Anzeige

When we have completed all the steps, we finish the recording in the register:

Developer Tools” – “Stop Recording

See picture:

Record macro in Excel 2016
Create a macro in Excel
Makro in Excel 2016 erstellen
Insert empty line in dynamic table
Stop macro recording in Excel
4x CHIP TESTSIEGER Handyversicherung_600x500
Anzeige

3. Insert buttons in Excel.

3. Insert buttons in Excel.

Of course, we would have been able to set a keyboard shortcut to create our macro in this way. But since we like to have it comfortable first, and secondly, probably after some time of non-use anyway no one has noted the shortcut, we will use a button for this purpose.

It’s always there, and it just looks fancier

To add a button, click on the tab:

“Developer Tools” “Paste”

and find out there the element “button” out.

So that our created button also executes our macro, we have to assign the corresponding macro to it.

To do this, right-click on the button, select “Assign Macro” and select the macro from your list (if you have created several macros).

Of course, you can also assign a unique name to the button by right-clicking on “Edit Name“.

See picture: (click to enlarge)

Insert button in Excel 2016
Rename button in Excel2016
Assign macro to Excel 2016

You can now transfer all entries from one list to another at the touch of a button.

As you have seen, the process is far less complicated than it first appeared. Of course, you can use the macro function in Excel to automate significantly more complex processes, which is why you are welcome to be creative at this point.

And always remember: Excel does not bite 😉

Print Friendly, PDF & Email

Of course, we would have been able to set a keyboard shortcut to create our macro in this way. But since we like to have it comfortable first, and secondly, probably after some time of non-use anyway no one has noted the shortcut, we will use a button for this purpose.

It’s always there, and it just looks fancier

To add a button, click on the tab:

“Developer Tools” “Paste”

and find out there the element “button” out.

So that our created button also executes our macro, we have to assign the corresponding macro to it.

To do this, right-click on the button, select “Assign Macro” and select the macro from your list (if you have created several macros).

Of course, you can also assign a unique name to the button by right-clicking on “Edit Name“.

See picture:

Insert button in Excel 2016
Rename button in Excel2016
Assign macro to Excel 2016

You can now transfer all entries from one list to another at the touch of a button.

As you have seen, the process is far less complicated than it first appeared. Of course, you can use the macro function in Excel to automate significantly more complex processes, which is why you are welcome to be creative at this point.

And always remember: Excel does not bite 😉

Print Friendly, PDF & Email
Become Guest Writer

We publish your guest contribution free of charge in German and English
incl. backlink to your website and identification of the author

Anzeige
Blogverzeichnis Bloggerei.de

About the author:

Michael Suhr
Michael SuhrWebdesigner / Economist
After 20 years in logistics management, I have been working as a freelance web designer and office trainer since the beginning of 2015. Incidentally, I give tips and tricks for more digital skills in my blog as time permits.

Search by category:

Anzeige
Die Amazon.de VISA Karte
 Preis nicht verfügbar zum Angebot*
Preis inkl. MwSt., zzgl. Versandkosten
*Zuletzt aktualisiert am 18. October 2018 um 0:41 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

About the author:

Michael Suhr
Michael SuhrWebdesigner / Economist
After 20 years in logistics management, I have been working as a freelance web designer and office trainer since the beginning of 2015. Incidentally, I give tips and tricks for more digital skills in my blog as time permits.

Search by category:

Anzeige
Die Amazon.de VISA Karte
 Preis nicht verfügbar zum Angebot*
Preis inkl. MwSt., zzgl. Versandkosten
*Zuletzt aktualisiert am 18. October 2018 um 0:41 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.
2018-09-29T11:16:37+00:00By |Categories: Excel 2016, Office 2016|Tags: , |
In order to optimize our website for you and to be able to continuously improve it, we use cookies. By continuing to use the website, you agree to the use of cookies. Ok