VBA code to set zoom level for all sheets in workbook!!!

Zoom level in excel is one of the important aspect when we share it with end users. Sometimes, it is mandatory to have specific zool level set to each sheet in workbook. As we know, we can adjust zoom of a single worksheet at a time. So, it become a monotonous job when there are hundreds or thousands of sheets include in a workbook. Someone will be tired to adjust zoom level of numerous sheet in the workbook. However, using excel macro we can set specific zoom level to all sheets in the workbook. it can be done within a minute as soon as macro executed.

VBA code to Rename multiple files in a folder with a excel macro !!!

Rename multiple files from a folder is one of the craziest job. if someone is dealing with hundreds or thousands of files in a folder. To rename individual file one by one will definitely make someone crazy. As it is a monotonous task, which can lead to commit many mistakes while rename filenames individually. Moreover, there is not a single function available in excel to perform these job easily.

VBA code to zip multiple files!!!

Zip multiple files is a routine activity for file processing task. We zip multiple files to share our data to end users. We know zip files are very useful to compress files and folder which can be easily shared via email. Most of the times we need to work with multiple zip file simultaneously. We often zip multiple files once our data is processed which is a manual task usually. However, to perform end to end project automation zipping task can be automated. let's see how can we use excel macro to zip multiple programmatically.

VBA code to unzip files from multiple zip files!!!

Unzip files is one of the regular task in our daily activity. Thus, most of us often prefer zip files to publish our data to end users. As, we know zip files are extensively use to compress files and folder which can be easily shared with business owners. Sometimes, we need to work with multiple zip file simultaneously and we need to unzip multiple files. Therefore, it takes lot of time to unzip multiple zip files for that instance. However, using excel macro this task can be automated. let's see how can we use excel macro to unzip multiple zip files on a single go.

VBA code to list all image from the workbook!!!

Images are widely used in excel reports to make it more interactive and presentable. Thus, images are mostly used in various excel reports. Sometimes, we use name of images in excel macro to automate report generation process. Therefore, it is very important to use required naming convention to each image used in the workbook. Preferably, some sort of images validation would be the best solution to avoid runtime errors which can occured by incorrect image name. Using excel macro code we can list all images from the workbook which is further used in image name validation.

VBA code to list all add-ins in the workbook!!!

Add-ins is very useful functionality in excel application. Which helps user to implement specific calculation or requirement for a specific dataset. Add-ins is nothing but a generic macro which is created to perform specific task in excel as same as excel macro. Sometimes, we need details of specific Add-ins which is being used in excel workbook. In this article, we will see how to get details of each add-ins using a excel macro.

VBA code to delete columns from worksheet by excel macro?

Column in excel is very important element for data manipulation operation. Which stored various kind of data based on column type. It may contain numbers, text, currency, data and so on. Basically, it used to perform various kind of data representation. It is a basic member of table which is known as field in database terminology.

In some situation, we need to delete certain columns from the excel worksheets. Depends on number of columns it will take efforts to perform the task. However, this task can be automate using excel macro.
Using excel macro you can delete required columns from the worksheet. It will hardly take a minute to perform this task.

VBA code to list all comments to a new worksheet in workbook!!!

Comment in excel is a way of annotate calculation logic of certain measure or KPI. It will be very useful to transfer data manipulation logic user to user. As a results, there is extension use of comments take place in a workbook. It will be very difficult to check each and every comments if comments scattered in different ranges in the workbook. Thus, it will be a quite problematic task to gather information of all the comments on the singe place.

In this situation a simple excel macro will be very helpful to gather all comments in a list. Using excel macro you can list all the comments within a minute.

VBA code to delete specific name ranges in a workbook!!!

Excel application works on concept of cell which is nothing but intersection of row and column. However a single cell is called as a range which is a most important member of excel object hierarchy. Various formulae in excel application will work on the ranges to get final result. Ranges can be named to a object know as Named Range which will be further used in macro code to perform certain data manipulation.

Sometime, we need to delete specific named range in the workbook which will not be used furthermore. Delete specific named range will be quite time consuming when there are large number named ranges created in the workbook. We can simply automate this task by adding few line of code in macro.

VBA code to list all name ranges in a workbook!!!

Name range is one the useful feature to provide name to a cell or group of cell in excel worksheet. Many a times, user needs multiple named ranges in excel workbook. Then it becomes very difficult to list down all the names ranges created in each worksheet. We can automate this task and list down all named ranges from excel workbook.

Excel VBA code to list folder and total size!!!

Sometimes, we need list of system folder and their size in MB. This is not a regular task, but it takes a lot of manual effort when such kind of requirement occurs. Thus, it's need a automation which will give us list of all subfolders and their size. Fortunately, this task can be automated using a small excel macro which will provide list of all subfolder and their size.

How to call a macro from another workbook?

Executing macro from same workbook is a routine task in Excel automation. Many a times, We face a situation where we need to a call macro from another workbook. Sometimes, it's very useful to call a macro from another workbook which will help us optimize macro code. Also, it is very easy to modify the code in individual macro of separate workbook.

Taking consideration of these advantage of macro with separate workbook will be used to automate tasks which scheduled on specific time intervals.

VBA code to delete multiple sheets at once!!!

Most of the time we face a situation where we need to delete multiple sheets for a workbook. it become a monotonous job when there are large number of sheets which should be deleted. Sometime, because of repetitive task user accidentally deletes useful worksheets. As a result, it consumes lot of productive time which is not a worth in reporting project.

How to refresh pivot table when source data update in Excel?

    Pivot table is one the most useful visualization is excel reporting projects. Its greatest strength is its ability to structure, summarize and display large amounts of data. Pivot tables can also be used to determine whether there is a relation between the row variable and the column variable or not. 

     Pivot table can display grand totals for columns, rows, or for the whole measure. It can also display subtotals for columns. Also, anyone can create a calculated field based on input data source. Using pivot table anyone can validate their input data in short time. which is very important feature in terms of data quality check.

How to validate if input file is excel workbook?

Input data validation is one of the most important activity in report generation process. Which will help user to avoid run time issues while performing report generation tasks. Thus, it is very vital to have input in proper format. To identify file could be a one of validation activity while automation the project.

This can be achieve with a small VBA function which will be used to identify if file is excel.

How select folder using excel VBA code?

Many a times we need to select folder to get input data path during project automation. Which we will be the first activity before macro execution. Selecting folder using dialogue box is very vital task any any automation activity. it helps as a input data validation to use correct input data in further execution. Also, it help to achieve data quality in report automation task.

Folder selection can be implemented by writing a short VBA code,

VBA code to delete multiple picture from worksheet !!!

Use picture and images is very good practice to make excel projects more interactive, Sometimes, there is a need to delete pictures from the workbook. It will be quite tedious task to select each picture and delete it manually.

Such task can be automate using small excel VBA code. Which will delete all pictures from active worksheet.

How can we send email with attachment using VBA in excel?

Sending email is a routine task for all kind of industry. it may be reporting, marketing, data science, communication, retail industry and so on. It will become a tedious job when same kind of email needs to be sent over multiple stakeholders.

     Obviously, sending same kind of email with different kind of attachment is a quite time consuming and boring activity. Specially, when it comes to email marketing to send emails to various recipient including attachments specific to their needs.

How to use SQL in excel VBA macros?

Using SQL queries is better way to handle large amount of data in database applications. also, it generates result within a seconds. As, we aware the SQL queries only can be used in Microsoft Access and Microsoft SQL server applications.

In fact, SQL queries can be used in Excel VBA projects to handle large amount of data. Which will enables you to optimize your code for time complexity and output will be generated in fraction of seconds. Which is the core requirement of any VBA automation project.

How to create new folder at runtime using excel VBA?

Maintain folder structure is a vital task in  automation projects. Sometimes, it is very essential to delete and create folders at runtime. to achieve this task user needs to add a code which will be create folder at runtime.

Since to create specific folder at runtime, user need to automate this task in excel macro itself. Automating new folder creation task not only maintain folder structure but also ensures quality of work if there are several several folders to be deleted and create at runtime.

How to delete a excel workbook from selected folder?

Delete specific workbook or excel file is a usual task in many reporting project. As a routine activity user needs to delete old report and create new one based on latest data. Performing delete activity seems to be very risky when it is a manual, specifically when folder contain several report with slightly change in names.

Since to delete old files from the specific folder, user need to automate this task in excel macro itself. Automating file deletion task not only accomplish 100% quality but also saves a lot time to delete old files if there are several files to be delete from the folder

Useful VBA code to switch between tabs on the ribbon in MS Word and Excel !!!

Switch between various tab is one the very useful activity for certain VBA automation. Which is different for excel and word VBA programming. I got trick to navigate tabs on MS-Word or MS-Excel ribbon programmatically.

Most useful VBA code to delete unused items and refresh pivot table in activeworkbook

Pivot in excel is most useful functionality to aggregate and summarize huge data. It is one the most useful reporting tool in excel which used to represent meaningful data from a large data set. Many a times back end data get refreshed for these pivots. Which generates missing items in pivot table which not require anymore.
Removing missing items from various pivots sometime would be a quite time consuming task.

How to check the size of each worksheet of workbook?

Excel is highly recommended reporting tool which used in diverse industry for reporting purpose. Though, it is very useful by it's various reporting functionality. It has some limitation which will be taken into consideration while generation reporting tools. Workbook size is one of the limitation which slow down performance of reports which contains huge data. There could be various causes which impact on file size. In fact, to overcome this file size limitation, we identify the sheet which is result in increase in file size and optimize it to decrease overall size on disk.

Finding size of each sheet is not a simple task. But, it can be possible with excel VBA code. Here is the code which will give you size of each sheet in your report.

How to save bulk email attachments from outlook to a specific folder on PC?

Microsoft Outlook is one the best email processing application used by user on daily basis. Sometimes, it is used for bulk data transfer medium. users sending bulk emails consist of lots of attached file of various extension such as excel (Microsoft Excel), word (Microsoft Word), PDF (Adobe), Text files, Images (Photoshop file) and so on. Then, it becomes very tedious job to handle large number of attachment in Microsoft Outlook. Saving bulk attachment into specific folder takes lot of productive time which definitely impact on other activities.

Using excel VBA code this task can be automate which will definitely save lot of productive hours. This, VBA code will take hardly 5 minutes to save attachment into specific folder of your Personal Computer.