You can generate a Pivot Table report with a mouse click. You can do it using Excel VBA and macros!

Let’s suppose you want to generate a Pivot Table report based on a given Excel table created; see below…

Figure 1: Source data for Pivot Table report

This would be the resulting PT report (a simple one):

Figure 2: simple Pivot Table report

If you do it by hand, this would be the process:

Creating the PT report by going to: Table Tools>Design>Summarize with Pivot Table (assuming the Excel table is already created)

Setting row fields (“Products”)

Setting data fields (“Units Sold”)

Overall, the same steps should be executed using VBA, see the Excel macro code below…

Public Sub CreatePivotTable()
'developed by John Franco
'excelgurusacademy.com
'last update: Mar-03-2012
Dim MyPTReport As PivotTable 'a pivot table report object
Dim MyPTCache As PivotCache 'the pivot table data
On Error Resume Next
'adds a new sheet
Sheets.Add
'creates a pivot table based on Table1. You can change your table name as you like
' Create PivotTable cache and report.
Set MyPTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="Table1")
Set MyPTReport = ActiveSheet.PivotTables.Add(PivotCache:=MyPTCache, TableDestination:=Range("A1"), _
TableName:="PivotTable1")
'add row fields
With MyPTReport.PivotFields("Product")
.Orientation = xlRowField
.Position = 1
End With
'add data fields
MyPTReport.AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Units Sold"), "Sum of Units Sold", xlSum
End Sub

That’s all! You can create a Pivot Table report each time without effort.

Download the Excel Pivot Table macro practice file at the bottom of this page.

Now let me explain the Excel VBA macro code in more detail…

Adding a new Pivot Table using VBA

To create a new Pivot Table in a workbook you need to be familiar with the hierarchy of Pivot Tables objects in the Excel object model, see the picture below…

Figure 3: Pivot Table object model

Basically you need to specify the unambiguous path that points to your PT, e.g.

Workbooks(“YOURBOOK”).Worksheets(“YOURSHEET”).PivotTables(“YOURPIVOTTABLE”).”PT method or property here”.

You can make your Excel VBA code shorter when you are referring to the active workbook, replace the entire VBA objects hierarchy: “Workbooks(“YOURBOOK”).Worksheets(“YOURSHEET”)” by this one: “ActiveSheet()…”

Well, to create the Pivot Table invoke the “Add” method of the “PivotTables” collection.

PivotCache – this provides the data for the report

TableDestination – the cell where the PT report will be inserted, e.g. “A1”, “G2”, etc.

TableName – the name of the Pivot Table, e.g. “Region sales”, “Labor hours summary”, etc.

Here’s the full code I used above:

Set MyPTReport = ActiveSheet.PivotTables.Add(PivotCache:=MyPTCache, TableDestination:=Range("A1"), _
TableName:="PivotTable1")

You first need to specify the source data of your future Pivot Table (cache). To create a “PivotCache”, use the “Create” method of the “PivotCaches” collection.

This is the syntax:

expression.Create(SourceType, SourceData, Version) or you can also use expression.Add(SourceType, SourceData, Version)

“expression” represents a “PivotCaches” object.

Where the two most important arguments are:

SourceType – can be either: xlConsolidation, xlDatabase, or xlExternal

SourceData – this is the data source of your Pivot Table, e.g. Range(“A2:F22”), Sheets(“Sheet3”).Range(“F30:G40”), “Table1”, etc.

This is the full code I used above:

Set MyPTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="Table1")

Now is time to add the respective fields…

Adding row fields to a Pivot Table using VBA

Just for refreshing the location of Pivot Table fields, here’s the PT report layout:

Figure 4: Pivot Table fields’ layout

To add row fields use the “PivotFields” collection of the “PivotTable” object.

Specifically, use the “Orientation” property of the “PivotField” object.

This is the syntax:

expression.Orientation

expression represents a “PivotField” object.

Specify the orientation (field type) using the respective “Orientation” property value:

xlRowField

xlPageField

xlColumnField

xlDataField

This is the full VBA code I used above…

With MyPTReport.PivotFields("Product")
.Orientation = xlRowField
.Position = 1
End With

At this time you have defined a blank PT report, see below…

Figure 5: Blank Pivot Table report, before adding fields

It’s time to add stuff to it…

Adding data fields to a Pivot Table using VBA

You need to use the “AddDataField” method of the “PivotTable” object.

The syntax is:

expression.AddDataField(Field, Caption, Function)

“expression” represents a “PivotTable” object.

Where the main parameters are:

Field – what field you refer to, it should be one of the fields contained in the source data (cache). For this example the fields are: “Product”, “Salesman”, “Units Sold” or “Price per unit”

Caption – how your field will be presented in the Pivot Table report, e.g. “Sum of Sales”, “Average of price”, etc.

Function – to sum use xlSum, to average use xlAverage, etc.

This is the full code I used above…

MyPTReport.AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Units Sold"), "Sum of Units Sold", xlSum

Additional VBA Pivot Table techniques

Adding column fields using VBA

Use also the “PivotFields” collection, see the full VBA code below…

'define column field
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product")
.Orientation = xlColumnField
.Position = 1
End With

Adding page fields using VBA

Use also the “PivotFields” collection, see the full VBA code below…

'add page fields
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product")
.Orientation = xlPageField
.Position = 1
End With

Converting the current region to a table

If you want to create the Excel Table on which your Pivot Table report will be based on (“Table1” for this example), you need to use the “Add” method of the “ListObjects” collection. See the full VBA code below…

'creates a table based on current selection
ActiveSheet.ListObjects.Add(xlSrcRange, Range(Range("$A$1").CurrentRegion.Address), , xlYes).Name = "Table1"

Excel VBA Downloads

Download the Excel PivotTable macro practice workbook below:

According to this Office article, there are 477 functions in Excel 2016. They are distributed this way:

Category

Amount

Statistical

112

Math and trigonometry

80

Financial

55

Engineering

54

Compatibility

38

Text

33

Date and time

25

Lookup and reference

24

Information

20

Database

12

Logical

11

Cube

7

Web

3

Add-in and Automation

3

Total

477

The new Excel 2016 functions are shown below:

Function name

Category

Description

IFS

Logical

Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.

SWITCH

Logical

Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned.

FORECAST.ETS

Statistical

Returns a future value based on existing (historical) values by using the AAA version of the Exponential Smoothing (ETS) algorithm

FORECAST.ETS.CONFINT

Statistical

Returns a confidence interval for the forecast value at the specified target date

FORECAST.ETS.SEASONALITY

Statistical

Returns the length of the repetitive pattern Excel detects for the specified time series

FORECAST.ETS.STAT

Statistical

Returns a statistical value as a result of time series forecasting

FORECAST.LINEAR

Statistical

Returns a future value based on existing values

MAXIFS

Statistical

Returns the maximum value among cells specified by a given set of conditions or criteria

MINIFS

Statistical

Returns the minimum value among cells specified by a given set of conditions or criteria.

CONCAT

Text

Combines the text from multiple ranges and/or strings, but it doesn’t provide the delimiter or IgnoreEmpty arguments.

TEXTJOIN

Text

Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.

The functions that were renamed and moved to a different category are shown below:

Function name

Category

Description

Note

BETADIST

Compatibility

Returns the beta cumulative distribution function

In Excel 2007, this is a Statistical function.

BETAINV

Compatibility

Returns the inverse of the cumulative distribution function for a specified beta distribution

In Excel 2007, this is a Statistical function.

BINOMDIST

Compatibility

Returns the individual term binomial distribution probability

In Excel 2007, this is a Statistical function.

CHIDIST

Compatibility

Returns the one-tailed probability of the chi-squared distribution

In Excel 2007, this is a Statistical function.

CHIINV

Compatibility

Returns the inverse of the one-tailed probability of the chi-squared distribution

In Excel 2007, this is a Statistical function.

CHITEST

Compatibility

Returns the test for independence

In Excel 2007, this is a Statistical function.

CONFIDENCE

Compatibility

Returns the confidence interval for a population mean

In Excel 2007, this is a Statistical function.

COVAR

Compatibility

Returns covariance, the average of the products of paired deviations

In Excel 2007, this is a Statistical function.

CRITBINOM

Compatibility

Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value

In Excel 2007, this is a Statistical function.

FDIST

Compatibility

Returns the F probability distribution

In Excel 2007, this is a Statistical function.

FLOOR

Compatibility

Rounds a number down, toward zero

In Excel 2007 and Excel 2010, this is a Math and trigonometry function.

FTEST

Compatibility

Returns the result of an F-test

In Excel 2007, this is a Statistical function.

GAMMADIST

Compatibility

Returns the gamma distribution

In Excel 2007, this is a Statistical function.

GAMMAINV

Compatibility

Returns the inverse of the gamma cumulative distribution

In Excel 2007, this is a Statistical function.

HYPGEOMDIST

Compatibility

Returns the hypergeometric distribution

In Excel 2007, this is a Statistical function.

MODE

Compatibility

Returns the most common value in a data set

In Excel 2007, this is a Statistical function.

NEGBINOMDIST

Compatibility

Returns the negative binomial distribution

In Excel 2007, this is a Statistical function.

NORM.INV

Compatibility

Returns the inverse of the normal cumulative distribution

In Excel 2007, this is a Statistical function.

NORMDIST

Compatibility

Returns the normal cumulative distribution

In Excel 2007, this is a Statistical function.

NORMSDIST

Compatibility

Returns the standard normal cumulative distribution

In Excel 2007, this is a Statistical function.

NORMSINV

Compatibility

Returns the inverse of the standard normal cumulative distribution

In Excel 2007, this is a Statistical function.

PERCENTILE

Compatibility

Returns the k-th percentile of values in a range

In Excel 2007, this is a Statistical function.

PERCENTRANK

Compatibility

Returns the percentage rank of a value in a data set

In Excel 2007, this is a Statistical function.

POISSON

Compatibility

Returns the Poisson distribution

In Excel 2007, this is a Statistical function.

QUARTILE

Compatibility

Returns the quartile of a data set

In Excel 2007, this is a Statistical function.

RANK

Compatibility

Returns the rank of a number in a list of numbers

In Excel 2007, this is a Statistical function.

STDEVP

Compatibility

Calculates standard deviation based on the entire population

In Excel 2007, this is a Statistical function.

TTEST

Compatibility

Returns the probability associated with a Student’s t-test

In Excel 2007, this is a Statistical function.

VAR

Compatibility

Estimates variance based on a sample

In Excel 2007, this is a Statistical function.

VARP

Compatibility

Calculates variance based on the entire population

In Excel 2007, this is a Statistical function.

WEIBULL

Compatibility

Calculates variance based on the entire population, including numbers, text, and logical values

In Excel 2007, this is a Statistical function.

ZTEST

Compatibility

Returns the one-tailed probability-value of a z-test

In Excel 2007, this is a Statistical function.

EXPON.DIST

Statistical

Returns the exponential distribution

In Excel 2007, this is a Statistical function.

FORECAST

Statistical

Returns a value along a linear trend

In Excel 2016, this function is replaced with FORECAST.LINEAR as part of the new Forecasting functions, but it’s still available for compatibility with earlier versions.

This short story shows how I gained the respect and trust of 3 departments in the big company I worked for.

Before I knew how Pivot Tables in Excel worked, I found a big opportunity to become valued at my office…

…I noticed that one single Excel table contained all the data that the Planning, Cost, and Engineering department needed.

This was a huge realization at the time…

…in a way, the data and its insights were dormant!

The Planning department needed the length of pipes by diameter and by zone, the Cost department needed pipes length by diameter for suppliers, the Engineering department needed the total length of pipes to plan the work of drafters.

And nobodywas providing these insightsin a timelymanner!

I knew Pivot Tables were good for mining data but I didn’t understand how they worked yet.

And the table was monstrous…

I have always known the benefits of trying new things so I created a Pivot Table report out of the table, and started playing with it.

I asked to each department head, what do you want to know about this data?
The table contained tens of fields but I just focused on the fields they care about: Pipe length, Pipe zone, Pipe Diameter, etc.

For example, Mirian, from the Planning department wanted to know the distribution of pipes by zone, so I dropped those fields into the Pivot Table Excel canvas. And patterns started emerging. I did a lot of trial and error until the pattern she wanted was established.

Do it now!

Pick your company data and see how you can help different people, maybe your boss and the boss of your boss. Ask them what do they want to get out of this data? And they will tell you because people is craving for more understanding.

Drag and drop the most relevant category fields (Department, Pipe diameter, Name, Year, etc.) to the Row area of your PT report. Drag and drop the most important data fields (Length, Sales, Amount, etc.) to the Value area of your PT report. If you are not satisfied with the layout, move the Pivot Table Excel fields around. This is the way I did it and it worked because any layout you create is right (maybe not needed) and you can play safely until youfind something that is needed.

The worst part was when they started asking me for new reports. My trial-and-error approach was a huge limitation because it was embarrassing playing with data when they were looking over my shoulder.

HopefullyI stumbled upon a simple grocery analogy that revealed the mechanics of Pivot Tables in Excel and you can grasp it in minutes from now.

If you’re looking for an “insider” shortcut guide to finally get comfortable with Pivot Tables in Excel, creating insightful Pivot Table Excel reports (consistently) that will supercharge your data mining and presentation skills, then you’ve got to sign up for my free Excel Pivot Table Tutorial.

The reason why many Excel users never learn how to do a Pivot Table in Excel is because they don’t understand why they should learn Pivot Tables neither what is a Pivot Table.

If you are still afraid of Pivot Tables or you want to establish a solid foundation to your data analysis journey, in this free Excel Pivot Table tutorial, you will learn the fundamentals first:

I was one of those who was not aware of the psychology and mechanics of Excel Pivot Tables.

Why you shoud learn how to do a Pivot Table in Excel

But I was determined to definitely get it because I realized Pivot Tables were a form of fast Excel dashboarding and fast data analysis and understanding.

Create Excel dashboards fast

You can create summarized tables out of big tables with a couple of clicks. You can add several views of the same data set (e.g. by department, by quarter, by region, sum, average, count, etc). You can also add charts and dashboard controls such as slicers and timelines without using VBA, without writing any single vlookup or index and match formula.

I created the plain dashboard you see below in a matter of 5 minutes!

Analyze and understand data

For example you can turn a big 10K rows Excel table into a tiny Excel table packed with meaningful information because a Pivot Table finds patterns and show them to you. For example, in the table below, the thousands of “Delivery Truck” entries become a single one with a subtotal.

When you learn how to do a Pivot Table in Excel, you become able to include in your analysis only the data that matters most to you. For example, if you are a manager, you can focus on departments or products or on both categories at the same time. You can focus your analysis even further by looking at a date range only. If you are a salesman, you can focus your analysis on the product you sell and narrow the date range to your sales period.

If you want to look at your data from another vantage point, you can do it easily because you can drag and drop any data field you want and change your Pivot Table report instantly.

Well, if you don’t know how to do a Pivot Table in Excel yet, then you need to learn what is a Pivot Table first. If you want to get Pivot Tables explained to you in a meaningful manner, you might find this explanation useful:

What is a Pivot Table

You are about to discover a simple analogy that will make Pivot Tables easier to learn and use.

If you don’t know what is a Pivot Table, if you are afraid of learning and using Pivot Tables, if you have only a basic idea of Pivot Tables in Excel and don’t know what to expect when you drop the fields to the PivotTable report, or if you just want to understand more about Pivot Tables, then keep reading…

…because this grocery analogy I stumbled upon will show you how to do a Pivot Table in Excel because it will make PTs simpler for you, and it will boost your reporting and dashboard skills, starting today.

It’s a shame…

I used to create Pivot Tables reports through trial and error but I was afraid my coworkers or boss would ask to me: how did you that? because I didn’t know exactly how my Pivot Table reports were generated, how the fields affected the layout of the report, and so forth.

I never had confidence in using Pivot Tables because it was a sort of hope-reporting. Every column, row, page, or value field I dropped into the fields’ area, produced unexpected results.

Everything changed when I discovered the Pivot Table relationships . Pivot Tables Paradigm!

A Pivot Table report is like a box. See below:

Let’s imagine the box contains oranges, apples, lemons and bananas. This is your source data.

With this bunch of fruits as your source data, you can ask questions like: how many oranges, apples, lemons and bananas are in the box? You can achieve this by counting each type of fruit.

To better find the answer, you organize the box by fruit type as you see below:

Here’s the answer and a nice subtotal:

You can also organize the fruits according to its type: citric and other.

If each fruit have a price tag, then you will be able to answer questions like: how much the oranges, apples and bananas are?, how much the box is?, how much the citrics are?

To answer the question: how many green and mature oranges contain the box?, you would need to compartmentalize the box by fruit state: mature and green oranges.

Very simple, right?

To answer the question: how many big or small green/mature oranges are in the box?, you would need to compartmentalize the box by fruit size.

Would you compartmentalize the entire box again? Or just the existing compartments?

You would separate green oranges into small and big and mature oranges into small and big.

In other words, you compartmentalize the already compartmentalized box.

This simple principle applies to Excel Pivot Tables…

Learn how to do a Pivot Table in Excel

You simply group the data source on your Pivot Table report by any of the fields you choose.

The first item you add to your Pivot Table fields area (box) is what’s in your box (data source), the second field is how you compartmentalize the existing box, the third field is how you compartmentalize the existing already compartmentalized box, and so on.

For example, if you have a table with these fields: Department, Brand, Model, Salesman, Date, Units sold, Amount, and you add the “Department” field to the fields area, then you will answer questions like how much sales by department.

To answer the question how well each brand performed by department?, then you would add “Brand” to the fields’ area so you compartmentalize “Department”. If you add Brand, then you compartmentalize department by brand, if you add salesman, you split the brand compartment by brand, and so on.

You now should be able to drag and drop the Pivot Table report fields with confidence.

If you are more comfortable with using Pivot Tables in Excel, and want to get deeper into using Pivot Tables, you can definitely learn how to do a Pivot Table in Excel in my Free Excel Pivot Table tutorial

Or you can explore these good Pivot Table tutorials:

The best way to go through cells dynamically is using the Cells object inside a loop. This way you can refer to a different cell on each pass of the loop.

Use a simple counter variable (you can name it whatever you want), which is increased or decreased on each loop pass. Here are some examples…

Using a ForNext loop

Use For Next when you know the starting row/column as well as the last row/column.

For counter = 2 To 5
Cells(counter, 5).Value = Cells(counter, 3).Value * Cells(counter, 4).Value
Next

Using a DoWhile loop

Use Do While when you either don’t know the starting row/column or the last row/column.

You can run an Excel macro by clicking any Excel object you can imagine: text box, shape, picture, chart, etc. See below the main Excel objects you can use…

Go to the ribbon Insert>Illustrations>Shapes>then, add any of the available shapes. See the picture below…

You can also insert any other type of illustration like: Picture, Clip Art, SmartArt, etc. You can use any already-created button. Additionally, you can also use other Excel objects like charts, equations, etc.

Once the shape/object is in your sheet, you are ready to link an Excel VBA macro to it.

2) Assign a macro to your button/object

Right click over the existing shape or object and then choose “Assign Macro…” on the contextual menu. See the picture below…

Then, specify the source location of your macro in the “Macros in:” drop-down list of the “Assign Macro” dialog (“This Workbook”, “Personal Macro Workbook” or any specific workbook). See the picture below…

Important notice: if your macro is not stored centrally in the Personal Macro Workbook or as an Add-in, you need to open the macro source-workbook before you assign those macros to any button. Read “how to store Excel macros for future use“

Then, select the Excel macro you want to be attached to the button, and then click OK.

Your Excel macro is now attached to your button!

You can also create a new macro that will be automatically attached to the current button. Just click “New” or “Record…” in the “Assign Macro” dialog.

You are almost done, just a final step to make your Excel macro button more reusable…

3) Name your Excel macro button meaningfully

Don’t use names as “Click here” or “Macro 1”, use meaningful names that describe the macro purpose, for example: “Delete selected columns”, “Insert financial formula”, etc. This way you will use the button naturally and without hesitation.

To do it, proceed this way…

Right click over the shape (Text Box, Shape, Equation), then click “Edit text”, then enter your text.

In the case of pictures and charts you can add a Text Box to describe the assigned-macro, it could be something like “click the picture to format columns”, etc.

And be creative with the layout of the button, have fun. Select the shape and then go to Drawing Tools>Format>Shape Styles, there you can change the shade, color, borders, etc.

Here are some examples of Excel macro buttons…

Have button ideas to share? Let us know at the comments section below…

If you acquire a brand new car and ignore the location of the keys, you couldn’t open the doors to get in, nor drive the car. In a sense, when you don’t know the location of your recorded Excel macros or written Excel VBA macros you won’t be able to get in the VBA editor to edit the Excel macro or run the macro.

Knowing the location of your Excel VBA macros is essential if you want to create, edit and run your Excel macros!

If you ask some of the questions below, then this Excel VBA article is for you…

Will my macro be there if I close my workbook or Excel?

How can I share my Excel macros with others?

How to use the Excel macros from others?

How to use a macro I wrote or recorded before?

How to make my macros available on other Excel VBA workbooks?

How to create a macro in Excel?

How to write a macro in Excel?

It’s very simple!

You just need to be aware of some details I will share with you here…

You can think on a workbook as a box with two compartments, one for spreadsheet-data and the other one for VBA-data. In other words, an Excel VBA workbook might contain spreadsheet-data and VBA-data, see the illustration below…

Now, you might ask, “if an Excel macro is stored inside a VBA workbook, will it be available to other workbooks?”

That’s a good question.

The answer is NO, a macro is only available to the same workbook where it was created, but you can make it available to other workbooks, you need some tricks you will learn in just a moment.

First let’s explore the difference between…

Local Excel macros vs. central Excel macros

Let’s suppose you create a macro in Excel VBA, which is a loan calculator app and it works this way: first you enter the arguments like loan amount, annual interest rate, loan period in years, etc, then you hit calculate, finally the Excel macro generates the monthly payments in the same sheet.

You don’t want to run this macro on other workbooks because the relevant data is entered in the given loan sheet. This is a local macro.

Some examples of local macros might be:

Fixed-layout constrained macros. For example, a macro that works upon a fixed table scheme or report

Sheet-specific macros. For example, a macro that uses the data of a particular sheet or sheets

One-time macros

Etc.

Now think for a moment in a macro you created in Excel to format a raw sales table you receive every week. The macro deletes some columns, sorts the table by invoice number, adds some headings here and there, etc. Every time it does the same to the file you receive weekly.

You want to use this macro each time you receive new data. This macro is required to be centrally stored so is available on each upcoming sales workbook.

Repetition routines like: converting date format, inserting a given formula in the current cell, etc.

Below you will explore how to make the macros you create in Excel (or record) available to the same workbook or to any other workbook (local or central)…

Location of recorded Excel macros

If you know how to record a macro in Excel, then you know that when you hit stop, your recorded macro is somewhere in your hard drive and this location depends on what you specified in the “Record Macro” dialog, see the picture below…

Important notice: by default, your recorded Excel macros are named Macro1, Macro2, MacroN and stored in Module1, Module2, ModuleN of the specified workbook.

Make your recorded macros available to the same workbook only (local)

1) Choose “This Workbook” or “New Workbook” in the “Store macro in:” drop down list of your “Record Macro” dialog (see “Record macro dialog” picture above).

2) Then record your Excel macro as normal.

Run that macro by specifying the source workbook where you recorded the macro (it should be open). See the picture below…

Make your recorded macros available to any workbook (central)

1) Choose “Personal Macro Workbook” in the “Store macro in:” drop down list of your “Record Macro” dialog.

2) Then record your Excel VBA macro as normal.

You don’t need to open the source file to run your central macros because the “Personal Macro Workbook” is always loaded. See the picture below…

What’s the Personal Macro Workbook?

It’s a hidden Excel 2007 Binary File workbook (.xlsb) that is always open so you can run its macros all the time. See the picture below…

If you edit any macro of the “Personal Macro Workbook” and close Excel, you are presented with the message below (This is a handy proof this book is open behind the scenes)

The “Personal Macro Workbook” is stored by default in this location: C:/Users/Administrator/AppData/Roaming/Microsoft/Excel/XLSTART

Important notice: show system folders this way (system folders are hidden by default). In the Windows explorer window, go to Organize>Folder and search options>View>Hidden files and folders>Show hidden files, folders, and drivers. See the picture below…

Location of the Excel macros you create

You are free to put the Excel macros you create or write using VBA anywhere inside your Excel VBA compartment (VBA editor). You can store your Excel macros in any module and in any Sub procedure. For example: you can create a module called MyFormulas and create the macros (procedures) MyAdvancedFormula1, MyAdvancedFormula2, MyFinancialFormula1, etc. Or you can create a module called MyCommonRoutines and write the macros: FormatMyReport, DeleteMyColumns, etc.

Here’s an illustration of the location of your macros inside the VBA editor (compartment)…

This is how your Excel macros are stored inside the VBA editor…

You can also put the created macros in the “ThisWorkbook” VBA module or in any VBA Sheet module (under the folder Microsoft Excel Objects) of the VBA Project explorer pane.

Important notice: Sub procedures must be Public in scope so you can call them from outside the source module, e.g. from another module, from a button, shortcut, toolbar, ribbon, etc.

Make your written Excel macros available to the same workbook only (local)

Just write your macros in any module of your VBA editor (workbook compartment). It will be available next time when you open that workbook.

Your local macros will be listed in the Macro dialog, after you select “This workbook”, “All Open Workbooks” or any specific workbook in the “Macros in:” drop-down list. see the picture below…

Make your written Excel macros available everywhere (central)

If you want to run a macro from any other workbook, see the picture below…

You can run a macro in Excel in several ways…

1) Write your macro in the Personal Macro Workbook, it will be available next time on any workbook you open.

This method is not recommendable because you can’t control the location of the master macros book and you can’t organize your macros in meaningful files., e.g. a workbook for formatting macros, another one for calculations, etc.

The best way to do it is by storing your workbook as an add-in. Here’s how to do it…

1) Write your macro in any given VBA module of a new or existing workbook

2) Save your book as an Excel Add-in, go to Office button/File>Save As>Save as type:>set the file type as add-in, see below…

.xlam – Excel Add-in workbook format in Excel 2007+

.xla – Microsoft Excel Add-In format in Excel 97-2003

3) Go to Office button/File>Options>Add-Ins>Manage:>Excel Add-ins>Go

4) Then in the Add-ins dialog activate the desired add-in file, if is not listed here, choose “Browse…” and locate your add-in file

Now you can call your macros using any of the methods below…

Are you asking where the macros in a workbook are?

Well, you should know that Excel stores VBA stuff: macros, modules, user forms, etc. as well as spreadsheet data inside workbooks.

How does this work? Here’s a quick explanation…

To access your macros VBA code you need to open the workbook compartment that contains VBA data, this is the VBA interface.

There you will find the macros VBA code (procedures) in modules, see the picture below…

Launch the VBA editor this way…

Excel 2007/2010 users…

The fastest way to show the VBA interface is by using the shortcut ALT+F11, type it when you are in the workbook environment.

You can also use the Ribbon. Excel 2007/2010 brings the “Developer” tab hidden by default. Activate it this way…

If you are using Excel 2007 go to the Microsoft Office button>Excel Options>Popular>”Show Developer tab in the Ribbon”>OK

If you are using Excel 2010 go to the Microsoft Office button>Excel Options>Customize Ribbon, and then activate the “Developer” tab, see the picture below…

Once the Developer tab is shown in the Excel ribbon, activate it and click on the “Visual Basic” toolbar.

Another handy way to show the VBA editor is by right clicking over any sheet tab of your workbook and choosing “View code”.

You can also go directly to any given macro by going to the Ribbon: Developer>Code>Macros>Select your macro>Edit. See the picture below…

Excel 97-2003 users…

Click ALT+F11 or go to Tools>Macro>Visual Basic Editor.

The same right click technique applies to Excel 2003, just right click over any sheet tab of your workbook and choose “View code”.

You can also go directly to any given macro by going to the Tools>Macro>Macros>Select your macro>Edit.

How to record a macro in Excel that works dynamically on different array sizes and layouts

Put your cursor at the starting position of your raw data (Don’t include earlier mouse movements in your Macro)

Launch the Record Macro dialog

Assign a shortcut to your Macro (so you can run it repeatedly easily)

Click Ok on the Record Macro dialog

Use relative references (On Excel 2003 do it this way: click the Relative Reference button on the Stop Recording toolbar. On Excel 2007 do it this way: go to View>Macros> Macros>Use Relative References. On Excel 2016 go to Developer>Code>Use Relative References). Do this before you perform any movement of the Excel cursor around your spreadsheet

Record one instance of the dynamic Excel Macro (if you have one thousands blocks of data, record just one)

Stop the Macro when your mouse is at the starting position of your next block of data (corresponding position to the location you started on step 1 of this tutorial)

Congratulations! You have recorded a dynamic Excel Macro.

Now it’s time to use the dynamic macro you just recorded.

How to use a dynamic macro you recorded in Excel

Use your dynamic Macro this way:

Put your cursor at the starting position and then press the Macro shortcut. If you want to process N blocks of data, press CTRL + YOURKEY N times, or keep your shortcut pressed and the Excel Macro will be repeated effortlessly.

For instance, you can use this dynamic macro recording technique to convert raw data like this one:

Into table layout like this one:

Your recorded Macros will work for one row or for one million rows, for one column or one hundred columns.