VBA-Excel: Create worksheets with Names in Specific Format/Pattern.

  Download Link:Worksheets Many times we find ourselves in a situation where we want to allow users to add the new worksheets and the sheet name must follow some specific format or pattern. Recently I have received the same request from one our reader. This made me realize that it’s a very common problem so … Read more VBA-Excel: Create worksheets with Names in Specific Format/Pattern.

VBA-Excel: Add Worksheets For All The Given Dates Except Weekends and Copy The Common Template In Each Worksheet

  Download Link: Multi Sheets Objective: This tool is very useful. Many times we encounter a situation where we need to maintain a report on the daily basis (For weekdays). We manually create a worksheet for each day and copy the template from previous sheets and edit it. This tool will automate all the manual … Read more VBA-Excel: Add Worksheets For All The Given Dates Except Weekends and Copy The Common Template In Each Worksheet

VBA-Excel: Create or Add Worksheets at the Run time.

Objective: This article will teach you “how to add worksheets in excel workbook at the run time”. Important Command: Workbook. Worksheets.Add().Name = “NameYourSheet” Complete Code: Sub Sumit() Dim mainWorkBook As Workbook Set mainWorkBook = ActiveWorkbook mainWorkBook.Worksheets.Add().Name = “Sumit” End Sub Output:   Also Read: Create worksheets with Names in Specific Format/Pattern. Add Worksheets For All … Read more VBA-Excel: Create or Add Worksheets at the Run time.

VBA-Excel: Modified Consolidator – Merge or Combine Multiple Excel Files Into One Where Columns Are Not In Order

Download Link: MergeExcel This is the extension of my earlier article “Consolidator“. In this article we will modify it further. Suppose we have a scenario where we have multiple excel files with same columns but they are not in the same order. See the example below. How to Use it: Down­load the MergerExcel.xlsm from the link … Read more VBA-Excel: Modified Consolidator – Merge or Combine Multiple Excel Files Into One Where Columns Are Not In Order

Excel-VBA : Open a Excel File using Another Excel File using Browse Option.

This tutorial will teach you how to Open a Excel File using Another Excel File using Browse Option. We will be using Application.GetOpenFile() function to accomplish this.

NOTE: You can define any type file you want to , just set the proper filter.

FileFilter:=”Excel Files *.xls* (*.xls*) – to open Excel files

FileFilter:=”Excel Files *.txt* (*.txt*) – To open Text File

NOTE :Do not provide any filter if you want to select any type of file as attachment

Complete Code:

Read moreExcel-VBA : Open a Excel File using Another Excel File using Browse Option.

Excel-VBA : Insert Multiple Images from a Folder to Excel Cells

Say you have many images in a folder and you want to insert all these images in your excel work book, one image in one cell. You can do it manually, insert and resize the image and drag it to the particular cell, but think of a scenario where you have more than 100 images or may be more. I am sure you don’t want to do it manually. IF you are looking for solutions for problems like this, this is the tutorial for you.

This tutorial will teach you about how to insert multiple images from a folder into your excel.

Steps:

Excel-VBA : Change Passwords for all the WorkSheets in one shot

Objective : Suppose you have many worksheets in an excel and you want to change all the passwords in one shot rather doing it manually for every sheet, which might be very bad way to do specially when you have a power of doing it using VBA.

NOTE: you need a place to store your password, most of the timewe store it in our mind but computer doesn’t have mind, it has memory :), but the point is where, it could be a file, DB . Why not use your excel for that.

Store it in a separate sheet and make it very hidden in your code.

Read moreExcel-VBA : Change Passwords for all the WorkSheets in one shot

VBA-Excel: Consolidator – Merge or Combine Multiple Excel Files Into One

Download Link : Consolidator_1.0

If you want to combine many excel files into one file and you don’t want to do it manually, you have come to the right place. This piece of software will allow you merge as many excel files you want, say 500-1000 excel files. This tool will provide you various options of formatting before you merge your files.

How to Use it:

  1. Download the consolidator.xlsm from the link provided at the top and at the bottom of this article.
  2. Place all the excel files, which you want to combine, into one folder (make sure all files are closed).

Read moreVBA-Excel: Consolidator – Merge or Combine Multiple Excel Files Into One

VBA-Excel: Get all the WeekDays or Working days in Specified Date Range, (excluding Satudays and Sundays)

This tutorial will teach you how to get all the working days or weekdays in a specified date range(excluding Satudays and Sundays), simple but very useful when you have come up with a excel where you have to work with all the business working days. Example: If the date range is mentioned between 8-Jan-2014 to … Read more VBA-Excel: Get all the WeekDays or Working days in Specified Date Range, (excluding Satudays and Sundays)

VBA-Excel: User Forms

User Forms are one of the very important features in Excel Macro. It provides and an interface to the user for interaction with the workbook. It has many controls which makes user’s life easy for entering the data from excel in a specific manner or reading the particular data from the excel.

Getting Started : Create a Blank User Form

  1. To open or create a new user form, follow the steps below
  2. Open an Excel file
  3. Press “Alt+F11” to open the Microsoft Visual Basic Editor.
  4. Make Project Explorer and Property Window visible from “view menu” (If not already visible)
  5. Select the Project from the Project Explorer.
  6. Form “Insert” menu, select “User Form
  7. Read moreVBA-Excel: User Forms

VBA-Excel: Application.Wait OR Wait Method

Description:

The Application.Wait () function pauses the running code or macro for the time specified and once that time is passed, it returns True value.

Format:

Expression.Wait (Time)

  • Expression : A variable that represents an Application Object.
  • The Wait method stops all the Microsoft Excel activities and prevent all the major operations.
Arguments:

VBA-Excel: Add/Insert multiple objects from a folder in an Excel Document.

To Add or Insert Multiple objects from a folder in Microsoft Excel, you need to follow the steps below:

  • Create the object of FileSystemObject(Link)
  • Create Folder object using FileSystemObject and GetFolder (link) method and count the number of files.
  • Get the files Object
  • Run the Loop for each files in folder
  • Insert the File objects , using OLE objects.
  • Insert the added files name list in another sheet(just for reference)
  • Save the workbook

Read moreVBA-Excel: Add/Insert multiple objects from a folder in an Excel Document.

VBA-Excel: Fill Excel Range Values in a 2D Array

You can fill a range values from an excel work book into a two dimensional array. All you have to do is assign range values to a variable and then that variable will become array

Two Dimensional Dynamic array -1
Two Dimensional Dynamic array -1

Function FnFillValues()

Read moreVBA-Excel: Fill Excel Range Values in a 2D Array

VBA-Excel: Convert Numbers (Rupees) into Words OR Text – Updated Till 1000000 Crore With Decimal Numbers

To Convert Numbers into Text, please follow the steps below Download Link: NumberToWords Example: 1000000000.00 Rupees One Hundred Crores 345.56 Rupees Three Hundred Fourty Five and Fifty Six paise only 12312312.00 Rupees One Crore Twenty Three Lacs Twelve Thousand Three Hundred Twelve 999999999.00 Rupees Ninty Nine Crores Ninty Nine Lacs Ninty Nine Thousand Nine Hundred … Read more VBA-Excel: Convert Numbers (Rupees) into Words OR Text – Updated Till 1000000 Crore With Decimal Numbers

VBA-Excel: Create Dynamic or Run-time WorkSheets using Macro.

To Create Dynamic or Run-time WorkSheets using Macro, follow the steps mentioned below

Steps:

  • Get the Active WorkBook object
  • Add the worksheets at the run time using Add().Name() method

 

Get the Active WorkBook object

        Set mainWorkBook = ActiveWorkbook

Add the worksheets at the run time using Add().Name() method

Read moreVBA-Excel: Create Dynamic or Run-time WorkSheets using Macro.

VBA-Excel: SUDOKU Solver

Download Link : SUDOKU Solver

Steps:

  • Open a new WorkBook
  • Create a SUDOKU template
  • Create a Button
  • Copy Paste the Complete Code
  • Press the button till the SUDOKU is solved.

Note: This Code works only for easy and medium level SUDOKU problems

 

After Creating the SUDOKU template you page should look like this:

NOTE: Make sure you create the template in correct cells else you will have to change the code

Create a SUDOKU template

SUDOKU SOLVER-1
SUDOKU SOLVER-1

Read moreVBA-Excel: SUDOKU Solver