Excel VBA loop worksheets in the same workbook

Posted on March 4, 2015May 19, 2016 by Wyman W

This tutorial explains how to loop worksheets in the same workbook, and delete worksheet, add worksheet based on worksheet name You may also want to read: Loop workbooks in folders and subfolders Excel VBA select multiple worksheets Excel VBA loop worksheets in the same workbook Loop though worksheets in the same workbook is useful when combined with If...Else statement. For example, you can loop worksheets to see which worksheet name contains a text in order to delete it, or add a wo...

VBA Excel Consolidate survey data

Posted on February 27, 2015May 12, 2016 by Wyman W

This tutorial explains how to use VBA Excel to consolidate survey data. Excel VBA Consolidate worksheets into one worksheet Excel VBA consolidate multiple workbooks into one workbook Excel VBA combine worksheets columns into one worksheet VBA Excel Consolidate survey data It is very difficult to describe what I meant to consolidate survey data, take a look at the below picture. The goal is to consolidate survey data as below. Normally, if you create online survey with Google, the r...

Auto Open Excel with Windows Task Scheduler

Posted on February 23, 2015February 23, 2015 by Wyman W

Auto Open Excel with Windows Task Scheduler Windows Task Scheduler allows you to trigger an event in Windows when specific criteria is met. In this tutorial, I will not detail what you can do with Task Scheduler (since there are too many features), instead I will focus on how you can auto open Excel with Windows Task Scheduler. Since I am using Chinese Windows but I want to write this article in English, all the screenshots are copied from the below website. http://www.sevenforums.com/tut...

Use VBA Excel Function to sort data in ascending order

Posted on February 11, 2015April 20, 2015 by Wyman W

This Excel tutorial shows how to use custom VBA Excel Function to sort data in ascending order instead of using Filter. You may also want to read Sort data using custom Function by assigning number to Text Sort data using Range Sort Method Sort data using Table AutoFilter Sort data using Worksheet AutoFilter Use VBA Excel Function to sort data in ascending order In this tutorial, I will show how to use VBA Excel Function to sort data instead of using Filter. This was origin...

VBA Access Excel Remove duplicates in text

Posted on February 10, 2015April 7, 2015 by Wyman W

This tutorial provides a custom Access Excel VBA Function to remove duplicates in text, parameters provide flexibility of case sensitivity and delimiter You may also want to read: Access Excel Scripting Dictionary store unique array item VBA Access Excel remove duplicates in text This tutorial is to help you to remove duplicates in text (in a Cell), but it is not about removing duplicated Cell value in a column. To remove duplicated Cell value in a column, highlight the column, navigate ...

VBA Excel combine columns depending on header name

Posted on February 9, 2015February 9, 2015 by Wyman W

Excel Combine columns depending on header name using VBA I have seen many times that people ask questions related to this topic, below are some examples 1) To copy specific columns and paste to another workbook 2) To distribute reports from a master table 2) To group specific columns to apply the same formatting In this tutorial, I will explain how to combine columns using different methods. Example 1 - when column name or number is unknown Scenario: In a worksheet called "maste...

Access Excel VBA extract number from text or extract alphabet

Posted on February 8, 2015September 4, 2017 by Wyman W

This tutorial shows several Access Excel VBA custom functions to extract number from text, extract alphabet from text, extract symbols from text Excel Access VBA extract percentage from text Excel Extract Time from Date Time or Extract Date Access Excel extract file name from file path Access Excel VBA extract number from text or extract alphabet from text In this article, I will show a list of custom Functions to perform the following tasks. - Check if a text contains any alphabet, ...

Excel VBA Extract columns to new workbook

Posted on February 7, 2015 by Wyman W

Excel VBA Extract columns to new workbook This was originally a question raised in Microsoft Community and was answered by me. I copied the original question below. I'm sure my issue is not unique. I have a excel document with hundreds of columns and only want about a dozen of them. I need to be able to extract specific columns to a new excel sheet as a repeated process without manual intervention. All I need is to pull certain columns into a new excel sheet from an excel document t...

VBA Excel Hide Blank Rows Automatically

Posted on January 28, 2015February 4, 2015 by Wyman W

VBA Excel Hide Blank Rows Automatically This is a question originally posted in Microsoft Community about Excel hide blank rows. https://answers.microsoft.com/en-us/office/forum/office_2007-excel/automatically-unhide-a-row-on-sheet-2-when-data-is/b1c85ae4-d0a8-4339-86c4-4ee68471ff73 The user has a worksheet called Sheet1 that is dynamically changed. Another worksheet, Sheet2, contains a range of formula that references to Sheet1. If the corresponding Sheet1 data is blank, Sheet2 Cell show...

Excel Auto run Macro when workbook is opened

Posted on January 23, 2015January 23, 2015 by Wyman W

Excel Auto run Macro when workbook is opened Excel Auto Run Macro is used when you need to call a Sub Procedure or run your actions when an Workbook is opened. For example, you can set special hotkey for Workbook when a Workbook is opened. One common use of Excel Auto run Macro is that people make application in Excel User Form and they want the Form look like a non-Excel, real Windows application. When user opens the Workbook, a Form pops up and the Excel application (the Excel background) ...