Add Sheets From Selection Excel VBA Macro

This simple macro example adds as many sheets to the workbook as cells in the selected range. The name of each sheet corresponds to the value in the cell. It is a quick way to format a workbook from a template of sheets to be added.

Add Multiple Series To Chart Excel VBA Macro

We can add a chart in Excel with just one line of VBA code specifying a range with source data. Then we can add as many series (i.e. lines, columns) as needed with an additional line for each new series. That may be needed to keep updating an existing chart with additional weekly or monthly data. In this post we add series that represent additional trials of an ongoing clinical test for a number of sample groups.

Extract Emails From Outlook Excel VBA Macro

We saw before how to send emails from Excelvia Outlook. We can also extract emails from Outlook into Excel. For that, we need to target the Outlook application object and specify the folder where emails are located (e.g., Inbox). Then we can choose to get all the emails in that folder, or have some conditions to filter for example only unread emails, or emails from a certain period, etc. The macro example below extracts all emails from the Inbox received since 01/01/2024.

Conditional Formatting For Another Cell Excel VBA Macro

In a previous post we saw how to apply conditional formatting rules in Excel with VBA macros. In that example, formatting was applied to a column based on unique cell values, thus highlighting each unique cell value with the same color. In this post we see how to apply conditional formatting to a column based on the value in another column. We can tweak the code slightly to highlight the entire row as well.

 

Send Google Mail From Excel VBA Macro

In a previous post we saw how to send emails from Excel via Outlook and from a Microsoft email account. We could use the same method to send email with Gmail by adding the Gmail account to Outlook and specifying to send using that account. However, if we don’t want to use Outlook at all, we can also send an email from Excel via a Gmail account using the Collaboration Data Objects (CDO) API for Windows. In this post we see a macro that sends an email from Excel using a Gmail account.

Export Range As Picture Excel VBA Macro

In this post we see how to export or save a range with data as a picture. This can be useful to share certain data, table, pivot, etc, as an image or paste it in some report. The macro copies the target range as a picture and pastes it into an empty chart of similar dimensions. Microsoft Excel allows to export a chart shape as a picture, hence using that object. Finally, the chart shape is deleted from the sheet. The picture is stored in the specified location with the given name and extension.

Sort Data With Custom List Excel VBA Macro

Microsoft Excel allows to sort a range based on cell values, cell and font color, and conditional formatting, in ascending or descending order, or using a custom list. In this post we see how to create a custom list of priority categories to sort a range with data accordingly. Excel includes built-in custom lists for weekdays and months. This macro example adds another custom list that is used temporarily to sort the range values. After sorting the table, the custom list is deleted.

Add Slicers To Table Headers Excel VBA Macro

Slicers are a cool feature initially used only with pivots that Microsoft Excel 2013 and following versions made available for data tables too. In this post we see how to add slicers in Excel using VBA macros. More specifically, the macro code below adds the slicers on top of table headers to allow filtering the table using the slicer instead of the drop-down filter option. It adds as many slicers as columns in the Excel data set that we want to filter.

Add Consecutive Numbers Excel VBA Macro

In this post we see how to add consecutive numbers to cells in the Excel worksheet using VBA macros. We review two simple options: 1. Using an classic programming For loop, and 2. Using the AutoFill function (a method of the Range object). Using AutoFill requires to add the first 2 numbers to set the sequence of the following numbers (option 2a), or adding just one number and choosing to auto fill with a series (option 2b).

Insert All Pictures In Folder Excel VBA Macro

In a previous post, we saw how to insert a picture from the web in Excel: Insert Picture From Web URL. We can insert a local picture using the same method, just replacing the URL with the file name and drive path where is located. In this post, we see how to insert all the pictures in a given folder into Excel using VBA macros. The macro inserts the pictures in a given cell or range and resizes them accordingly. We can also decide whether to insert the source information or just make an independent copy of the pictures.

Clear Cells Based On Value Excel VBA Macro

There are several ways to clear cells based on cell value with Excel VBA. The easiest, and probably also most efficient way to do it is using the Replace method of the Range object, which can replace the target value with an empty string in a given range or entire worksheet. Another possibility is looping through all cells in a range and setting a condition to clear the contents for a target value. This gets slower the bigger the range. On the other hand, this method allows not only to clear values, but also to change formatting or delete the target cell, for example. It also allows to set various conditions, instead of a single value.

Add Conditional Formatting To Unique Values Excel VBA Macro

Conditional formatting is a feature of Microsoft Excel that allows highlighting certain values to easily identify those cells. Excel offers various options for numerical values (greater or less than, between, top 10, data bars, color scales, icons, etc.), but there are less options for text. In both cases, we can set rules manually to apply formatting based on a given condition. This post shows how to apply rules automatically using Excel VBA macros. The macro specifically applies formatting to a column based on unique cell values. As a result, each unique cell value is highlighted in the same color.

Send Multiple Emails From Excel VBA Macro

In a previous post, we have seen how to send an email from Excel (via Outlook) using VBA macros. We can add as many recipients as needed separated by a semi-colon when sending one email. However, if we want to send separate emails to each recipient, we need to loop through the list of recipient emails and send an email to each of them individually. In this post, we see how to send multiple emails to recipients listed in a worksheet in Excel. We can choose to send the same or a different subject, body, or attachments to each recipient.

Convert Range To HTML Table Excel VBA Macro

In this post we see how to convert a range with data in Excel into an HTML table. We may want to get Excel data as an HTML table for example to add the table to a web page, to send an email with Excel data as a table in the body, or to show the data in a userform, among other things. We can see each separate use case in other posts. In this post, we will only see how to convert a selected range with data into an HTML table.

Export Table To Word Excel VBA Macro

We often copy/paste data, tables, and charts, from Excel to Word documents. In this post, we see how to automate that process with VBA macros. The code below copies a range with data in Excel, and pastes the content as a table in a Word document. The process can repeat or loop to copy various tables. The macro can be leveraged to copy other elements such as charts.

Add Button To Cell Right-Click Menu Excel VBA Macro

The cell right-click menu in Excel, also known as context menu, is a fast way to see and change properties of a cell or range in the worksheet. We can use VBA macros to add controls to that menu such as buttons that call a certain macro or run a function. This macro example targets the CommandBar object in order to add a button to the cell menu that runs a particular macro. We can add more buttons to the menu, or other controls such as sub-menus (popup control). Buttons can have a custom icon using the property FaceId. Find the list of icons available in Microsoft Office in this other page: Command Bar Button FaceId

 

Insert Pictures From Google Drive Excel VBA Macro UPDATE!

This is an UPDATE to a previous post where we saw how to insert pictures from Google Drive in Excel using VBA macros. The method explained before needs a slight change to continue working. That change consists of inserting the picture from Google Drive into a shape previously added to the Excel worksheet, instead of adding the picture directly to a range or cell. The macro requires access to the same Google Drive export view URL link including the picture sharing ID described in the previous article.

Import Specific Cells From Google Sheets Excel VBA Macro

In a previous post we learnt to import data from Google Sheets using a web query in Excel (Import Data From Google Sheets). We can actually create a web query in Excel either manually or with a simple VBA macro. In this post we see another method to import data from Google Sheets that allows getting specific rows, columns, and cells or values depending on certain criteria. In this other method, we send a HTTP request to the Google spreadsheet URL to get the data, and then we loop through the HTML response table to extract the values of interest according to a chosen criterion.

Unhide All Sheets And Cells Excel VBA Macro

In this post we see how to unhide all sheets and cells in Excel using VBA macros. The macro loops through each worksheet in the workbook to make any hidden or very hidden worksheet visible. At the same time and while in the loop, it also unhides all rows and columns, which subsequently unhides all the cells in that worksheet.

Separate Words and Numbers In Cell Excel VBA Macro

In this post we learn to separate words and numbers within cells in Excel using VBA macros. The macro splits the value in a cell into parts separated by a space (parts can be letters, words, numbers, etc). We can easily adapt the macro to separate the value by other character (a comma, a semi-colon, etc). In this particular example, we will get the keyword in that cell in one column, a number (if any) in another column, and a date (if any) in a third column.  The keyword can be based on a reference value or list of values, but in this case, we will just get the longest word in the sentence, assuming that’s the keyword in that sentence. If there is more than one number (or more than one date), the macro gets the last one. That can be easily changed as needed to get a number or date in a particular position.