Access Excel VBA copy workbook or file

This Access Excel VBA tutorial explains how to copy workbook or file. You may also want to read: FSO File Methods Worksheets.Copy Method to copy worksheet Access Excel VBA copy workbook or file There are two common ways to copy workbook or file FSO.CopyFile Method FileCopy Function I have explained how to use different FSO Methods in my previous post, click to see more details. Using FSO.CopyFile Method to copy workbook You should be able to run FSO in Excel 2013. If you fa...
More

Excel VBA combine rows into cell

This Excel VBA tutorial explains how to combine rows into Cell. You may also want to read: Separate line break data into different rows Delimit Cell value into rows Excel VBA combine rows into cell In my previous post, I demonstrated how to delimit a Cell into separate rows,  one example is that you copy a email list and you want to put each email address into different rows. From: To: In this  post I will do a reverse version, I will combine rows back into cell, so t...
More

Excel VBA find all Cells contain Named Range

This Excel VBA tutorial explains how to find all Cells contain Named Range using Macro. You may also want to read: Find all external links and broken links in workbook Replace Named Range with formula Find all Cells contain Named Range In Excel spreadsheet, we can define Named Range under Name Manager (Formulas > Name Manager) We can refer to Name Manager to see the formula the Named Range is referring to, but it does not tell which Cells are using the Named Range. Therefore...
More

Excel VBA Named Range Properties

This Excel VBA tutorial explains some common Properties of Name Object (Named Range), and demonstrate how to replace named Range with formula. You may also want to read: Excel find all external links and broken links in workbook Excel VBA Named Range Properties In Excel spreadsheet, we can define Named Range under Name Manager (Formulas > Name Manager) In the below screenshot, I have defined a Named Range called testNameRng, which refers to Cell A1 in Worksheet1. Currently A1 conta...
More

Excel extract text in brackets

This Excel tutorial explains how to extract text in brackets or in defined text in Excel spreadsheet and VBA. Extract text in brackets in Excel spreadsheet In the previous post, I demonstrated how to remove text in brackets or in any defined text, in this post I will explain how to extract the text in brackets or in any defined text. In Excel spreadsheet, you can extract any substring using Mid Function. To recap the syntax of Mid Function MID( text, start_position, number_of_characte...
More

Excel VBA select multiple files in dialog

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...
More

Access Excel capitalize first letter in a sentence

This Access Excel tutorial explains how to capitalize first letter in a sentence using VBA custom Function. Capitalize first letter in a sentence In Excel spreadsheet, there is a Function called Proper, which converts all the first letter of each word to capital letter. Example  Using Proper Function he is a boy. she is a girl. He Is A Boy. She Is A Girl. he is a boy. his name is John. mary is a girl. He Is A Boy. His Name Is John. Mary Is A Girl. Mr.  smith is happy ...
More

Access Excel remove text in brackets

This Access Excel VBA tutorial explains how to remove text in brackets using custom Function. You may also want to read: Change text color in brackets Extract text in brackets Access Excel remove text in brackets I was inspired to create this post because the HR system I am using (Workday) is very annoying, some data fields are actually a combination of two different fields, the additional data field is displayed in brackets in the prefix. For example,  an employee has a English name ...
More

Excel VBA change partial text color in Cell

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...
More

Access Excel VBA Array

This VBA tutorial explains how to create and use VBA Array in Access and Excel VBA. You may also want to read: Store unique array item with Scripting Dictionary Access Excel VBA Array In Excel worksheet, you can convert a formula arguments to Array by pressing CTRL+SHIFT+ENTER, click here to see example. In VBA, Array is a variable that can store multiple data of the same data type. There are two types of Array in VBA One-dimensional Array Two-dimensional Array For most...
More