This Excel VBA tutorial explains how to select multiple files in dialog using Application.GetOpenFileName. Excel VBA select file in dialog In many applications, you would see a "browse" button where you can select files for the application to process. In Excel VBA, you can create a Macro to open the browse dialog. After you select the target file, the file path is stored in a VBA Array, then you can loop through the array to get items, and then do whatever you like with the file. The...

This Excel VBA tutorial explains how to change partial text color in a Cell using Macro. You may also want to read: Access Excel remove text in brackets Change partial text color in spreadsheet In Excel spreadsheet, you can change part of the text in a Cell by highlighting the text and then change the font color. Suppose you want to change the text World in Cell A1 to red color. First, highlight the text.   Then select red color in the font color option. Change p...

This Excel VBA tutorial explains how to convert cell contents into table using Exel VBA. Excel VBA convert cell contents into table Yesterday when I tried to copy a table from a website to Excel, all the table contents are pasted into a single cell instead of copying the whole table. I believe there are workarounds to copy table but since this is an Excel blog, I want to write a Macro to convert cell contents back into a table. Previously I also wrote a post to convert cell contents into ro...

This Excel tutorial explains how to automatically refresh pivot table using Macro / VBA and Pivot Table Options. Excel automatically refresh pivot table You may have a pivot table that links to data in the same workbook or external data, and you may want to refresh pivot table automatically in order to see the most updated data. In this tutorial I will show several ways to automatically refresh pivot table. Method 1 - Automatically refresh pivot table when opening the file This is the simple...

This Excel tutorial explains how to delimit cell value into rows using VBA and non VBA. You may also want to read: Convert cell contents into table Access Excel custom split Function to delimit text Excel VBA separate line break data into different rows Excel VBA delimit Cell value into rows To demonstrate how to delimit Cell value into rows, I use email address as an example. One day I tried to copy a list of email addresses from Outlook into Excel.   When I past...

This Excel VBA tutorial explains how to sort worksheet tab in alphabetic order (ascending order or descending order). You may also want to read: Excel Range Sort Method to sort data Use VBA Excel Function to sort data in ascending order Access Excel assign number for custom sorting sequence Sort worksheet tab by name When you have a lot of worksheets in a workbook, you may want to sort them in alphabetic order for easy searching. As a behavior of Excel sorting, when an arr...

This Excel VBA tutorial explains how to consolidate multiple workbooks into one workbook. You may also want to read: Excel VBA Consolidate worksheets into one worksheet Excel VBA combine worksheets columns into one worksheet Consolidate multiple workbooks into one workbook Assume that you have a lot of workbooks, each workbook contains multiple worksheets. You may want to copy all worksheets of each workbook into a master workbook. For example, I have a folder called "sample" under...

This Excel tutorial explains how to use Excel VBA to reformat merged cells to row. You may also want to read: Fill Blank Rows or blank Cells Seperate line break data into different rows Excel VBA unmerge columns automatically Excel VBA reformat merged cells to row It is difficult to decide the title of this post, I will illustrate with an example. Imagine a Table with column A being a merged cell, and column B are the items under the same group. When you apply filter on c...