Excel VBA Worksheets.Copy Method to copy worksheet

This tutorial explains how to copy worksheets using Worksheets.Copy Method in Excel VBA, and demonstrate how to copy worksheet to another workbook. You may also want to read: Excel VBA Worksheets.Add Method to add new worksheet Excel Workbooks Open Method and check if workbook open Excel VBA Consolidate worksheets into one worksheet Excel VBA Worksheets.Copy Method to copy worksheet Excel VBA Worksheets.Copy Method is to copy worksheet in a workbook. You can copy worksheets from on...
More

Excel VBA DIR Function

This Excel tutorial explains how to use Excel VBA DIR Function and loop through workbooks in a folder. You may also want to read: Excel loop workbooks in folders and subfolders with FileSystemObject Excel VBA DIR Function DIR Function returns the first file name that matches the criteria in Function argument (pathname and attributes). DIR Function is usually used with loop in order to manipulate multiple files of specific name. Syntax of DIR Function Dir(pathname, [attributes]) ...
More

Excel export Excel to PDF with and without VBA

This Excel tutorial explains how to export Excel to PDF with and without VBA, how to export all worksheets to one PDF, and export all worksheets to separate PDF. You may also want to read: Select multiple worksheets using Excel VBA Excel export Excel to PDF In Excel, navigate to File > Save As, in the Save As Type,  you can save a file as different file types. Below is the available types in Excel 2013. In this tutorial we are going to focus on exporting Excel to PDF. Now se...
More

Excel consolidate all Comments in the workbook

This Excel tutorial explains how to consolidate all Comments in the workbook using Macro. You may also want to read: Excel show all comments in worksheet Excel delete all comments in Workbook Excel consolidate all Comments in the workbook Previously I wrote a post about how to show all Comments on the Cell (unhide it), in this tutorial I will show a Macro that consolidate all comments in the workbook. VBA Code - consolidate all comments Public Sub consolComments() counter = 1   ...
More

Excel show all comments in worksheet

This Excel tutorial explains how to show all comments and hide all comments in workbook. You may also want to read: Excel consolidate all Comments in the workbook Excel delete all comments in Workbook Excel show comments in worksheet Comment is a yellow dialog box that you can write comment regarding specific Cell. To insert a Comment, right click on a Cell and then select Insert Comment When we insert a Comment into a Cell, there would be a red triangle on the top right of ...
More

Excel verify Number format and convert Text to Number

This Excel tutorial explains how to verify Number format in a column. If it is a Text then convert Text to Number. You may also want to read: Excel verify Text format and convert number to text Excel Cell Function to check Cell format, address, contents Excel verify Number format Number can be in two forms in Excel - Text and Number. If a Number is a Text, there is a small triangle on the top left of the Cell. By default, number in Text format aligns to the left while Number alig...
More

Access Excel add leading zero

This Access / Excel tutorial explains how to add leading zero using Text Function or using custom Function to pad other text in suffix or prefix. Excel add leading zero (non-VBA) Number in Excel can come in two forms - Text or Number. For number in Number format, you may not add zero in prefix For number in Text format, you can add a zero in prefix In order to convert a Number to Text, add a ' in the prefix. For example, instead of typing 00123, type '00123 Sometimes you receive ...
More

Excel loop workbooks in folders and subfolders with FSO

This Excel tutorial explains how to loop workbooks in folders and subfolders with FileSystemObject (FSO). You may also want to read: Excel VBA Dir Function to loop through workbooks in folder Add password to all Excel workbook in folder Excel loop workbooks in folders and subfolders FileSystemObject (FSO) provides an API to access the Windows filesystem such as accessing Drive, TextStram, Folder, File. In this tutorial, since we are discussing how to loop workbook in folders and subfo...
More

Excel Range Sort Method to sort data

This Excel tutorial explains how to sort Excel data using Excel VBA Sort Method, and how to sort dynamic Range. You may also want to read: Sort data using custom Function by Bubble Sort Sort data using custom Function by assigning number to Text Sort data using Table AutoFilter Sort data using Worksheet AutoFilter Excel Range Sort Method to sort data There are mainly three kinds of sorting in Excel. 1 - Table AutoFilter sorting, using List Object to access AutoFilter. 2 - ...
More

Excel find all external links and broken links in workbook

This Excel tutorial explains how to find all external links and broken links in workbook using Find and Replace, Relationship Diagram, Macro. You may also want to read Excel VBA Workbook LinkSources Method Excel VBA refresh closed workbook Excel find all external links and broken links in workbook There are several ways to find external links and broken links in workbook, some are easy to use but have limitations, I will talk about each method in this article. Note that this article ...
More