Excel VBA Worksheets.Copy Method to copy worksheet

Posted on July 16, 2015January 26, 2017 by Wyman W

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

Excel VBA DIR Function

Posted on July 2, 2015July 2, 2015 by Wyman W

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]) ...

Excel export Excel to PDF with VBA

Posted on June 27, 2015March 31, 2019 by Wyman W

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 How to Convert Excel to PDF (non VBA) Excel export Excel to PDF (non-VBA) In Excel, you can easily export Excel to PDF by navigating to File > Save As, in the Save As Type, you can save a file as different file types. Below is the available types in E...

Excel consolidate all Comments in the workbook

Posted on June 26, 2015February 5, 2017 by Wyman W

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

Excel show all comments in worksheet

Posted on June 23, 2015February 5, 2017 by Wyman W

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

Excel verify Number format and convert Text to Number

Posted on June 22, 2015July 12, 2015 by Wyman W

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

Access Excel add leading zero

Posted on May 5, 2015December 26, 2016 by Wyman W

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

Excel loop workbooks in folders and subfolders with FSO

Posted on May 5, 2015March 26, 2021 by Wyman W

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 VBA search text in multiple Workbooks 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 discu...

Excel Range Sort Method to sort data

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

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

Excel find all external links and broken links in workbook

Posted on April 13, 2015December 26, 2016 by Wyman W

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