Excel » VBA » Macro »

Copy data from workbooks in folder and subfolders

I will in this article demonstrate a macro that automatically opens all workbooks in a folder and subfolders, one by […]

How to copy non contiguous cell ranges

If you try to copy multiple cell ranges on a worksheet that don't have the same number of rows or […]

How to quickly select blank cells

In this smaller example, column D (Category) has empty cells, shown in the picture above. If your column contains thousands of […]

How to add a macro to your Excel Quick Access Toolbar

The Quick Access Toolbar is located at the very top of your Excel window, I highly recommend that you place your […]

Opening a workbook runs a macro automatically

This article explains how to set up a workbook so a macro is executed every time you open the workbook. […]


A dialog box is an excellent alternative to a userform, they are built-in to vba and can save you time because […]

Find the longest/smallest consecutive sequence of a value [VBA]

This post Find the longest/smallest consecutive sequence of a value has a few really big array formulas. Today I would like to show […]

List comments [VBA]

Did you know that you can select all comments in the current sheet? Press F5, click "Special..." button, select "Comments" […]

Copy filtered Excel tables [VBA]

Today I want to share some pretty useful macros. My first macro copies an excel defined table with vba. It is […]

Add your personal Excel Macros to the ribbon

If you find yourself using the same macros over and over again, you can create a personal *.xlsb file that […]

Working with TEXT BOXES [Form Controls]

What's on this page Insert a text box Font and font size Add or edit text Position a text box […]

Show and hide a picture [VBA]

Have you ever seen dashboards where you can click a shape and a picture shows up. If you click it […]

Click a button to make specific worksheets hidden or visible (vba)

Click button "Show / Hide worksheets" to show or hide worksheets entered in cell range B6:B7. Instructions Here is how […]

Click a cell to make a column hidden or visible [VBA]

Click a single cell in column D to hide or show the comments in column E. Selecting multiple cells won´t […]

Scroll through a data set [VBA]

Sometimes you just want to show a small section of your data set, like in a dashboard or a chart. […]

Count unique distinct values by cell color

PRASHANT asks: i need to count unique number in a specific coloured cell for eg if there are coloured cells […]

Highlight date ranges overlapping selected record [VBA]

The following example shows you how to highlight overlapping ranges. How it works Select a date in the table. Conditional […]

Count text strings in formulas [VBA]

Rahul Jadhav asks: I have many excel files with multiple sheets and each excel sheet has many formula which are […]

Working with FILES

What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]

Change picture [VBA]

Rahul asks: i want to know that when we create a vlookup sheet, and in the name column we enter […]

Move a shape [VBA]

This post shows you how to move a shape with vba code. Select a cell (button name) and the arrow […]

Run a Macro from a Drop Down list [VBA]

This article demonstrates how to execute a VBA macro using a drop down list. The drop down list contains multiple […]

Hide specific columns [VBA]

Cyril asks how to hide all columns of a range except columns whose header is found in specific cells. I am […]

Copy excel table filter criteria [VBA]

Here is how to copy filter criteria from an excel table and use the same table filters on another table. […]

Excel calendar [VBA]

This calendar lets you schedule events on the data sheet. Select a cell (calendar date) and events on that day […]

Use filtered table values in a drop down list [VBA]

I read a very interesting blog post about Using Custom Functions in Dynamic Ranges Gabhan Berry creates a user defined […]

Sort values in a cell using a custom delimiter [VBA]

The following macro lets you select a cell range and a delimiting character. The macro sorts the values in each […]

Quickly create new sheets [VBA]

The following macro let´s you select a cell range and then the macro creates sheets with the same names as […]

Interactive Excel chart [VBA]

I found a basic interactive chart on the chitika website and my first thought was if I could do this […]

Sort values in an Excel table [VBA]

awall asks: Hey, can you do the opposite of this - not random order but this is my situation. I […]

Search two related tables simultaneously [VBA]

Let's say you do a lot of searches in two tables. The tables are related so it would be great […]

Select a cell in a table and the chart updates automatically [VBA]

The following vba code makes it possible to automatically update a chart when you click a cell in a table. […]

Excel template: Getting Things Done [VBA]

In this blog post I am going to demonstrate a simple workbook where you can create or delete projects and […]

Normalize data [VBA]

Debra has a great post and video about normalizing data for excel pivot table. This post describes a macro that normalizes […]

Add values to different sheets [VBA]

Phil asks: Could you please show me the code to place the copied data into a different tab instead of […]

Add values to a table [VBA]

Cyril asks: I do remember seeing one nice way of populating a table with the use of vba such as […]

Add or remove a value in a drop down list programmatically

This article demonstrates how to add or remove a value in a regular drop down list based on a list […]

Open Excel files in a folder [VBA]

This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]

Toggle a macro on/off using a button

This article demonstrates how the user can run a macro by clicking on a button, the text on the button […]

Automate data entry [VBA]

This article demonstrates how to automatically enter data in cells if an adjacent cell is populated using VBA code. In […]

Apply drop-down lists dynamically

This article demonstrates how to automatically create drop-down lists if adjacent data grows, there are two methods explained here. The […]

How to log when a workbook is opened and closed [VBA]

This article demonstrates how to automatically create log entries when a workbook opens or closes using event code. Column A […]

Auto resize columns as you type

Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all […]

Copy a dynamic cell range [VBA]

In this blog post, I will demonstrate some VBA copying techniques that may be useful if you don't know the […]

List all open workbooks and corresponding sheets [VBA]

In this post, I am going to demonstrate how to automatically create a new sheet in the current workbook and […]

Basic data entry [VBA]

In this small tutorial, I am going to show you how to create basic data entry with a small amount […]

List Excel defined Tables in a workbook [VBA]

The following macro inserts a new sheet to your workbook and lists all Excel defined Tables and corresponding Table headers […]

Working with COMBO BOXES [Form Controls]

This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]

Change chart data range using a Drop Down List [VBA]

In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]

Filter an Excel defined Table based on selected cell [VBA]

In this post I am going to demonstrate how to quickly apply a filter to a table. I am using […]

Populate a combobox with values from a pivot table [VBA]

In this post I am going to demonstrate two things: How to populate a combobox based on column headers from […]

Add values to a regular drop-down list programmatically [VBA]

In this tutorial I am going to show you how to add values to drop down list in cell C2. This […]

Filter an Excel defined Table programmatically [VBA]

In this tutorial, I am going to demonstrate how to filter an Excel define Table through a VBA macro. How it […]

Populate a list box with unique distinct values from a filtered Excel table [VBA]

Excel defined Tables, introduced in Excel 2007, sort, filter and organize data any way you like. You can also format […]

Populate a combo box (form control) [VBA]

In this tutorial I am going to explain how to: Create a combo box (form control) Filter unique values and […]

How to add a custom-made item to the shortcut menu [VBA]

This post describes how to add a new custom-built item to the shortcut menu in Excel, when you right-click a cell […]

Select cell A1 on all sheets before you close a workbook [VBA]

This post demonstrates a macro that automatically selects cell A1 on each sheet right before you close a workbook. The […]

How to save custom functions and macros to an Add-In

Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, […]

Use text qualifiers to make text to columns conversion easier [VBA]

This blog post describes how to insert qualifers to make "text to columns" conversion easier. Example I copied a table from […]

Create a Print button [VBA]

This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]

Select and view invoice [VBA]

This post demonstrates how to view saved invoices based on the invoice number using a userform. The userform appears when the […]

Populate listbox with unique distinct values [VBA]

This post demonstrates how to: Insert a button to your worksheet Assign a macro to the button Create a basic […]

Edit invoice data [VBA]

In a previos post:Excel vba: Save invoice data we added/copied data between sheets. This post describes how to overwrite existing […]

Save invoice data [VBA]

This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]

Consolidate sheets [vba]

Question: I have multiple worksheets in a workbook. Each worksheets is project specific. Each worksheet contains almost identical format. The […]

Rearrange values based on category [VBA]

In this post I am going to rearrange values from a list into unique columns. Before: After: The code Download […]

Split data across multiple sheets [VBA]

In this post I am going to show how to create a new sheet for each airplane using vba. The […]

Calendar with scheduling [vba]

Here is my contribution to all excel calendars out there. My calendar is created in Excel 2007 and uses both […]

Create a unique distinct list using Advanced Filter in a macro [VBA]

Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]

Identify missing numbers in a column

The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]