Der Blog für digitale Kompetenz

Der Blog für digitale Kompetenz

Use macros correctly in Excel

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.

But this feature has never been met before, as it has to be activated in the developer tools.

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 back button must be a book with seven seals.

How it is done in Excel 2016 is explained in our article.

Use macros correctly in Microsoft Excel 2016-2019

Topic Overview

Anzeige

Use macros correctly in Excel

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.

But this feature has never been met before, as it has to be activated in the developer tools.

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 back button must be a book with seven seals.

How it is done in Excel 2016 is explained in our article.

Use macros correctly in Microsoft Excel 2016-2019

Topic Overview

Anzeige

1. Enable developer tools in Excel

1. Enable developer tools in Excel

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’ve enabled your new additional Developer Tools tab in Excel.

See picture: (click to enlarge)

Entwicklertools in Excel
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’ve enabled your new additional Developer Tools tab in Excel.

See picture:

Entwicklertools in Excel
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 perform exactly the steps that are to be executed automatically at the push of a button.

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

“Developer Tools” – “Stop Recording”

See picture: (click to enlarge)

cshow
Ads
Makro in Excel aufzeichnen
Makro in Excel erstellen
Makro in Excel 2016 erstellen
Leerzeile in dynamische Tabelle einfügen
Makro Aufzeichnung beenden

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 perform exactly the steps that are to be executed automatically at the push of a button.

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

“Developer Tools” – “Stop Recording”

See picture:

cshow
Ads
Makro in Excel aufzeichnen
Makro in Excel erstellen
Makro in Excel 2016 erstellen
Leerzeile in dynamische Tabelle einfügen
Makro Aufzeichnung beenden

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 it firstly comfortable, 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” on “Paste”

and find out there the element “button” out.

In order for our created button to execute 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)

Button in Excel einfügen
Button in Excel 2016 umbenennen
Makro einer Schaltfläche in Excel zuweisen

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 ;)

Blogverzeichnis Bloggerei.de

Of course, we would have been able to set a keyboard shortcut to create our macro in this way. But since we like it firstly comfortable, 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” on “Paste”

and find out there the element “button” out.

In order for our created button to execute 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)

Button in Excel einfügen
Button in Excel 2016 umbenennen
Makro einer Schaltfläche in Excel zuweisen

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 ;)

Blogverzeichnis Bloggerei.de

Search for:

About the Author:

Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:

Search for:

About the Author:

Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:

Popular Posts:

2306, 2023

With the webcam to your dream job: Preparation for the video job interview!

June 23rd, 2023|Categories: Career, Hardware, Homeoffice, Shorts & Tutorials|Tags: , , |

Streamline your video interview with our tips on technique, setting, dress, body language and preparation. It's not quite success from the sofa, but this is where you can prove your professionalism.

1805, 2023

IT security: The deceptive appearance of digital security

May 18th, 2023|Categories: Data Protection, Hardware, Homeoffice, Software|Tags: , |

In a digitized world, IT security is an important factor. But technology alone is not enough. A holistic strategy is needed that also takes into account the human component.

2104, 2023

Create Excel Budget Book – with Statistics – Tutorial

April 21st, 2023|Categories: Internet, Finance & Shopping, Microsoft Excel, Microsoft Office, Office 365, Shorts & Tutorials|Tags: , , , |

Create your own Excel budget book with a graphical dashboard, statistics, trends and data cut-off. A lot is possible with pivot tables and pivot charts.

1504, 2023

Excel Database with Input Form and Search Function

April 15th, 2023|Categories: Microsoft Excel, Microsoft Office, Office 365, Shorts & Tutorials|Tags: , |

So erstellen Sie eine Datenbank mit Eingabemaske und Suchfunktion OHNE VBA KENNTNISSE in Excel ganz einfach. Durch eine gut versteckte Funktion in Excel geht es recht einfach.

Offers 2024: Word & Excel Templates

Special Offers 2024: Word Design CV-Templates

Monthly Technique Bestsellers:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

SmartHome | Energy & Security

SmartHome | Energy & Security

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Bestseller 2022-2023 Notebooks

PC & Accessoires

PC & Accessoires

Bestseller 2022-2023 Notebooks
Bestseller 2022-2023 Smartphones

Smartphone & Accessoires

Smartphone & Accessoires

Bestseller 2022-2023 Smartphones

Popular Posts:

2306, 2023

With the webcam to your dream job: Preparation for the video job interview!

June 23rd, 2023|Categories: Career, Hardware, Homeoffice, Shorts & Tutorials|Tags: , , |

Streamline your video interview with our tips on technique, setting, dress, body language and preparation. It's not quite success from the sofa, but this is where you can prove your professionalism.

1805, 2023

IT security: The deceptive appearance of digital security

May 18th, 2023|Categories: Data Protection, Hardware, Homeoffice, Software|Tags: , |

In a digitized world, IT security is an important factor. But technology alone is not enough. A holistic strategy is needed that also takes into account the human component.

2104, 2023

Create Excel Budget Book – with Statistics – Tutorial

April 21st, 2023|Categories: Internet, Finance & Shopping, Microsoft Excel, Microsoft Office, Office 365, Shorts & Tutorials|Tags: , , , |

Create your own Excel budget book with a graphical dashboard, statistics, trends and data cut-off. A lot is possible with pivot tables and pivot charts.

1504, 2023

Excel Database with Input Form and Search Function

April 15th, 2023|Categories: Microsoft Excel, Microsoft Office, Office 365, Shorts & Tutorials|Tags: , |

So erstellen Sie eine Datenbank mit Eingabemaske und Suchfunktion OHNE VBA KENNTNISSE in Excel ganz einfach. Durch eine gut versteckte Funktion in Excel geht es recht einfach.

Offers 2024: Word & Excel Templates

Special Offers 2024: Word Design CV-Templates

Monthly Technique Bestsellers:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

SmartHome | Energy & Security

SmartHome | Energy & Security

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Bestseller 2022-2023 Notebooks

PC & Accessoires

PC & Accessoires

Bestseller 2022-2023 Notebooks
Bestseller 2022-2023 Smartphones

Smartphone & Accessoires

Smartphone & Accessoires

Bestseller 2022-2023 Smartphones
Ads

Popular Posts:

Search by category:

Autumn Specials:

Anzeige
Go to Top