
Data & Developer Macros
+25 Excel Advanced Macros and Utilities for Mac
You can now use Excel's advanced features on your Mac, without any coding skills. Users already can easily and quickly Consolidate Workbooks, list, create, or analyze Tables & Pivot Tables, and use Power Query functions directly on your Mac with 101Macros Data Utilities. All of these cutting-edge add-ins and a set of Developer Macros are included in 101Macros Data Add-in.
All of these features are available in 101 Macros Suite.
Data & Developer
Macros Utilities
1. Consolidate Workbooks
Consolidate data from multiple worksheets using the Data Consolidation feature. This function combines data from multiple sources into a single, easy-to-update master worksheet, saving you time and reducing errors. Having all your data in one place makes it easier to manage and analyze.
Merge Sheets
in Workbook
Create a consolidated worksheet with data spread through your worksheet. Just select a cell in the data range, indicate the number of top rows, and 101 Macros will do the rest.
Merge Sheets from
Different Files
Create a consolidated worksheet with data from different files
(Excel, csv or text). Select the files to merge, the location of your data and
101 Macros will do the rest.
List Files in Folder
and Subfolders
Loop folders to get list contents. An index will be prepared with the files metadata and a hyperlink to the source.
How to use
Consolidate Workbooks
-
Consolidate Macros function can be accessed via:
a) through the 101 Macros Menu on the "Home Tab"
b) through the "Review Tab" -
A form will guide you through the process.
-
It creates a new file with the consolidated lists.
-
You can re-do this Macro with shortcut CTRL-OPT-Y.
.png)
2. Tables & Pivot Tables
Tools for creating/editing Tables and Pivot Tables, simplifying and speeding up the process of listing/creating tables from ODBC, Power Query, and M-Code. Unique utility for faster decision-making and better organization/access to essential information.
2.1. Table Macros
List All Tables
in Workbook
A clickable index with all tables will be created including name, location in workbook and data source
New Table from
Existing ODBC Connection
Create a new table from an existing ODBC connection.
New Table from
Existing Power Query
Create a new table from an existing Power Query Connection.
New Table from
M-Code
Create a new table directly from M-Code. Tip: You can get M-Code from all your Power Query Connections from the Power Query Submenu.
2.2. Pivot Tables Macros
List All Pivot Tables
in Workbook
A clickable index with all Pivot Tables will be created, including name, location, and data source.
New Pivot Table from
Existing ODBC Connection
Create a new Pivot Table from an existing ODBC connection.
New Pivot Table from
Existing Power Query
Create a new Pivot Table from an
existing Power Query Connection.
New Pivot Table
from M-Code
Create a new Pivot Table directly
from M-Code. Tip: You can get M-Code from all your Power Query Connections from the Power Query Submenu
Convert Pivot Table
into Values
Quickly convert a Pivot Table
to a Data Range.
How to use
Tables & Pivot Tables
-
Tables & Pivot Tables function can be accessed via:
a) through the 101 Macros Menu on the "Home Tab"
b) through the "Review Tab" -
For convenience, a contextual tab will be shown when ctrl-clicking (or right-clicking) on a Table or Pivot Table
-
You can re-do this Macro with the shortcut CTRL-OPT-Y.
Please note:
-
This Macro will not work on a protected workbook.

3. Power Query Macros
Easily import, export, and edit Power Queries and M-Code

Get M-Code for
All Power Queries
Export all Power Queries in the active workbook to a new table. You can use it as a 1-click backup system
for your M-Code
.png)
Update Power Query
M-Code
Select the Power Query to update the cell containing the reviewed M-Code.
Tip: You can quickly edit your M-Code after using our 'Get M-Code from All Power Queries' utility.
Please note no undo is available for this utility.

New Connection
from M-Code
Ideal for importing Connections from another workbook. Tip: You can quickly export your M-Code on the source workbook using our "Get M-Code from All Power Queries" utility.

New Table
from M-Code
Ideal for importing Connections from another workbook.
Data will be presented in a new table.
Tip: You can quickly export your
M-Code on the source workbook using our 'Get M-Code' from
All Power Queries' utility.
.png)
New Pivot Table
from M-Code
Ideal for importing Connections from another workbook. Data will be presented in a new Pivot Table.
Tip: You can quickly export your M-Code on the source workbook using
our 'Get M-Code' from
All Power Queries' utility.
.png)
Add Power Query Connections from Workbook Tables
Adds new Power Query connections with their corresponding M-Code for each table on your workbook.

Delete
Power Queries
Easily delete selected Power Queries from the list.
Please note: no undo is
available for this utility.
How To Use
Power Query Macros
-
Power Query Macros are available via the 101 Macros Menu on the “Home Tab” and the “Data Tab”.
-
We suggest you start by listing all your M-Code with our 'Get M-Code from All Power Queries' utility.
-
You can re-do this Macro with shortcut
CTRL-OPT-Y. (CTRL-ALT-Y)
Please note:
-
This Macro will not work on a protected workbook.

4. Developer Macros
Utilities for writing Visual Basic code faster
List all VBA Procedures
in Open Workbooks
This feature provides an index of all VBA procedures, including their file location, module, procedure name, and the number of lines of code in each procedure.
Copy VBA Module
to Another File
With this function, you can easily reuse your VBA macros by selecting the destination workbook and giving the new macro a name.
Export All VBA Modules
from Active Workbook
This feature allows you to easily back up your VBA macros by creating a file for each class, module, and form in the 101 Macros Temp Folder.
Import VBA Modules
from Folder
Restore your Macros from a Backup.
Tip: Backup your Macros often using 'Export All VBA Modules from Active Workbook' utility.
Delete All VBA Code
in Workbook
Delete all VBA code with one click.
This feature is a useful tool for users who need to start fresh or share their workbook without any VBA automation.
Please note there is no undo is available for this feature.
Get Environ
Variables
Environment variables change on every machine. Get a list fast with this utility to retrieve information about the environment variables. This information can be useful in many ways, such as customizing the behavior of a VBA macro based on the user's system environment or accessing information from other programs on the computer
Grant access to Mac
files and folders
Avoid frequent 'Grant Access' dialog boxes on mac. This feature is useful for users who work frequently with the same files and folders in Excel and wish to avoid interruptions to their workflow."
How To Use
Developer Macros
-
Developer Macros are available via the 101 Macros Menu on the “Home Tab” and the “Data Tab”.
-
We suggest you start by listing all your M-Code with our 'Get M-Code from All Power Queries' utility.
-
You can re-do this Macro with shortcut CTRL-OPT-Y. (CTRL-ALT-Y)
Please note:
-
This Macro will not work on a protected workbook or protected VBA modules.
