Excel Bible for Beginners Excel for Dummies Book Containing the Most Awesome Ready to use Excel VBA Macros (Suman, Harjit [Suman, Harjit]) (Z-Library)

Table of Contents INTRODUCTION Download the Free Macro Workbook Version Comments GETTING STARTED WITH VBA What is VBA? Why do People use VBA? Common VBA Terms Adding the Developer Tab to the Ribbon Visual Basic Editor Overview How to Insert a Module in the Visual Basic Editor (VBE) How to Run Macros WORKSHEET MACROS Unhide all Worksheets Hide all Worksheets Except the Active Sheet Sort Worksheets Alphabetically Unhide all Rows and Columns Unmerge all Merged Cells Auto Fit Columns Auto Fit Rows Protect all Worksheets Unprotect all Worksheets Insert Multiple Columns Insert Multiple Rows

Remove Wrap Text Delete all Worksheets Except the Active Worksheet WORKBOOK MACROS Create a Backup of the Current Workbook Close all Workbooks Copy Active Worksheet into a New Workbook Create an Email Message and Attach Active Workbook Attach Workbook to an Email HIGHLIGHTING MACROS Highlight Blank Cells Highlight Duplicate Values from a Selection Highlight Alternative Rows in a Selection Highlight Cells with Misspelled Words Highlight Specific Text Highlight Negative Numbers Highlight Cells with Comments CHART MACROS Create a Chart Format all Charts Add Chart Title Paste Chart as an Image FORMULA MACROS Remove Spaces from Selected Cells Convert Date into Day Convert Date into Year Remove Time from a Date Remove Date from Date and Time

Convert Text to Upper Case Convert Text to Lower Case Convert Text to Proper Case Convert Text to Sentence Case Word Count an Entire Worksheet Remove an Apostrophe Infront of a Number Remove Decimals from Numbers Multiply Numbers by a Specified Amount Remove Negative Signs from Numbers Replace Blank Cells with Zeros Insert A-Z Alphabets in a Range in Upper Case Insert A-Z Alphabets in a Range in Lower Case Remove Characters from a String Insert Degree Symbols PIVOT TABLE MACROS Create a New Pivot Table Delete all Pivot Tables in the Workbook Delete a Specific Pivot Table Hide Subtotals in a Pivot Table Unhide Subtotals in a Pivot Table Refresh all Pivot Tables in a Workbook Enable the GETPIVOTDATA Function Disable the GETPIVOTDATA Function ADVANCED MACROS Save a Selected Range as a PDF Convert Range to an Image Use Text to Speech

Activate a Data Entry Form Create a Table of Contents MORE BOOKS BY EXCEL MASTER CONSULTANT ABOUT THE AUTHOR

COPYRIGHT © 2021 Copyright Protection All rights rеsеrvеd. No part оf this publication may bе rеprоducеd, distributed, or transmitted in any form or by any means, including phоtоcоpying, recording, or оthеr еlеctrоnic or mechanical methods, without the prior written permission of the publisher, еxcеpt in the case of brief quotations еmbоdiеd in critical reviews and certain оthеr nоn-cоmmеrcial uses permitted by copyright law.

YOUR FREE BONUS GIFT! As a small token of thanks for buying this book I would like to offer a FREE bonus gift to all my readers. I am offering a FREE online VBA macros course called How to Record Macros in Excel . In this FREE course you will learn: How to record a macro to eliminate manual repetitive Excel tasks How to execute a macro by: Using the Macro Dialog Box Using the Visual Basic Editor Clicking a button Clicking a shape Once you have completed the course you will be able to: Automate Excel tasks easily

Save valuable time Advance your Excel skill s You can register for this FREE online VBA course by clicking on the below link and entering your details now. Register for the free VBA course

INTRODUCTION Macros are arguably the most powerful feature in Excel. They allow you to automate almost every task possible which will save you from doing mundane, repetitive tasks, enable you to become more efficient and ultimately save you time. This book contains the best and most useful ready-made Macros for you to use in your Excel workbooks. These Macros will enable you to have shortcuts in your workbooks so you don’t have to continually use the Excel ribbon to find the command buttons and perform the tasks manually, saving you hours each day and increasing your productivity. The best thing is, you don’t need VBA knowledge to use these Macros. You can be a complete Excel beginner and still use these Macros in your workbooks. All you need to do is copy these Macros from the free downloadable workbook (link is given in the next section of this chapter) and paste them in your workbook. If you want to learn VBA, you can instead write the VBA codes from this book into your workbooks so you get a feel of writing VBA code yourself. You will learn all about how to enter these Macros in your workbooks and how to run them in the next chapter. Although this book will give you some background in VBA it is not intended to teach you VBA. If you want to learn VBA you can enrol in my online VBA course for beginners called “Introduction to VBA Macros Course”. For more information about the course just click the link below: Introduction to VBA Macros Course Download the Free Macro Workbook To save you time, you can download the workbook which contains all the Macros in this book. All you need to do is copy the VBA code from the relevant Modules and paste them into the Modules in your

workbook. You will learn all about Modules in the next chapter. The link to download the workbook is below : Download the Free Macro Workbook How this Book is Structured The Macros in this book are organised under 7 sections: 1. Worksheet Macros 2. Workbook Macros 3. Highlighting Macros 4. Chart Macros 5. Formula Macros 6. Pivot table Macros 7. Advanced Macros You do not need to read this book in order. You can start with the Macros which you will find the most useful first. If you are new to VBA then I would highly recommend that you read the next chapter called “Getting Started with VBA” first as it will give you an introduction to VBA and show you how to insert Modules so you can copy and paste the ready to use Macros in your workbook. Who is this Book Aimed For? This book is aimed for people who want: To save time and effort as well as increase productivity in Excel Powerful shortcuts in their worksheets so they don’t have to continually use the Excel ribbon To avoid doing unnecessary, mundane repetitive tasks every day

To manipulate data in their worksheets quickly and easily without having to use complicated formulas To quickly create Excel’s more powerful features such as pivot tables and charts and manipulate them with a click of a button To automate their worksheets An overview of VBA as they would like to learn VBA coding Version I have written the Macros in this book using Excel 2013. These Macros will work in nearly all Excel versions, whether you are using an older Excel version or a newer one. Comments I am always interested in receiving feedback from you, either for this book or other books I have written. If you have any specific Excel topics you would like me to write a book about then please let me know. The best way to contact me is through my website: www.excelmasterconsultant.com

GETTING STARTED WITH VBA This chapter will give you some background about VBA and how to get started. Even though you don’t need to know how to write code to use these ready-made Macros, it is a good idea to know a little about VBA in case you would like to modify the Macros in the future. But first, let’s understand what VBA is. What is VBA? VBA stands for Visual Basic for Applications and it is a tool that is used to develop programs that allows Excel to turn complex or time- consuming tasks into automated processes. In simple terms, it is a language that allows you to communicate instructions to Excel using commands. Below are some key features of VBA: It is also used in other Microsoft Office programs such as Word, PowerPoint and even Outlook It is a standard feature of Microsoft Office products. You do not need to buy a copy You write a set of instructions using VBA code when you want Excel to do something The main purpose of VBA is to automate common repetitive tasks It is developed by Microsoft and was released in 1993 VBA programming is often called Object Oriented Programming (OOP) because you are working with objects. An object can be Excel itself, a workbook, a worksheet, a chart, a pivot table, a range and so on

Why do People use VBA? The main reason why people use VBA is to save them time by automating repetitive tasks such as copying and pasting. Below are some other common reasons why people use VBA in the workplace or at home: Format Worksheets Automatically – You may need to pull a report from another system such as a database on a daily or weekly basis but the report is not in the desired format. For example, there may be some columns you don’t need or the text is not in the correct font or size. Instead of having to manually delete columns and adjust text font and size each time, you can automate this with a Macro. Create User Forms for Data Entry – You can create a user form for data entry so that the data can be entered in a worksheet in a structured format. This is especially useful if there are lots of different people entering data in the same worksheet. You can create drop down boxes so you only enter data from the options given. Create Charts and Pivot Tables Automatically – You can write VBA code so that charts and pivot tables are created instantly which therefore saves you valuable time. Once they are created, you can automatically format them too. This book contains Macros that will allow you to do that. Automatically Create Emails and Attach Workbooks to It – You may need to send out a daily report at work which is time critical. Instead of having to write the email each time and attach your report to it, you can automate this with a Macro. The Macro can enter the email addresses, the subject, attach the

workbook and even write the body of the text and then send the email. Create Advanced Excel Models – For the more advanced user, you can create more complex models such as order forms which act like a shopping basket from an internet shopping site for example. You can also create advanced cost models which are not possible to create using just Excel. Common VBA Terms In this section I will list some of the common terms used in VBA which you may also see in the VBA codes later in the book. Comments You can add comments to your VBA code. This is very useful as you can explain what the blocks of code will do, especially if it is complex and hard to understand. You can also make notes as to why you are doing it. Comments are particularly useful if you need to go back to the VBA code and edit it after a lengthy time away to refresh your memory. Comments are inserted with an apostrophe (‘) at the beginning of the line of text. The line of text will turn green and VBA will just skip this line of code. Conditions Conditions work like an IF function in Excel. It evaluates a statement to test whether it is true or false. If the statement is true then it will execute a block of code. If the statement is false then it will execute another set of statements or do nothing at all. The main conditional statements in VBA are If….Then statements and Select Case statements.

Dim Dim is used to declare a variable to a specific data type. Declaring variables are good practice as it allows your Macro to run more quickly as it uses memory more efficiently. Loops Loops allow you to repeat a block of code a specified number of times or until a condition is met. Once the condition is fulfilled then the Macro will execute the next section of the code. There are various types of loops you can perform. The main ones are Do….Until, Do….While and For….Next. Methods Methods are actions you perform with an object. A method can change an object’s properties or make the object do something. Examples of Methods are selecting a range of cells, copying a range, pasting a range and clear contents in a range. Modules Modules are where Excel stores the VBA code. It is a container to hold all your VBA code. You can have just one Module to store all your VBA code or have multiple Modules to store your code. Objects In VBA we work with objects. There are over 250 objects we can work with. Examples of objects include workbooks, worksheets, ranges, cells, charts and pivot tables. Excel itself is an object.

On Error Statements On Error Statements allow you to perform error handling in your VBA code. The most common error handling statements you will see in VBA code are “On Error GoTo”, “On Error GoTo 0” and “On Error Resume Next”. Properties Properties are attributes that describe an object. An object’s property determines how it looks, behaves or whether it is visible or not. Examples of Properties include a cells font, the size of a cell’s font, the colour of a cell or whether the cell is left, centre or right aligned. Sub Procedure A Sub procedure contains a list of statements that performs a specific action. Every Macro starts with ‘Sub’ and ends with an ‘End Sub’. A Sub procedure is also known as a Sub routine. Variables Variables are values that are stored in the computer’s memory. You can specify a name for a variable and then use the variable in your VBA code. Adding the Developer Tab to the Ribbon The Developer tab contains some of the more advanced features of Excel including access to the Visual Basic Editor (VBE) to create your Macros. The Developer tab is used mainly by advanced Excel users and for this reason, Microsoft have hidden this by default. To unhide this tab however is very quick and easy. To get access to the Developer tab follow these instructions:

1) Right click any of the tabs and then from the shortcut menu select Customize the Ribbon 2) In the right-hand box check the Developer checkbox and press the OK button

3) The Developer tab is now visible Visual Basic Editor Overview The Visual Basic Editor (VBE) is where the Macros are stored. In order for you to write or copy and paste the Macros from this book into your workbook you will need to open the VBE. There are two main ways to do this:

1)  Click on the Developer tab in your ribbon and under the Code group select the Visual Basic command button 2)  Press the shortcut keys ALT + F11 Below is a brief overview of the VBE. Menu Bar In the menu bar you can customise the VBE such as making the Project Explorer and Properties Window visible. You can also perform various actions such as inserting a Module, saving your VBA code, inserting user forms and run Macros. Tool Bar Tool bars allow you quick access to various buttons. You can insert tool bars for editing your code, debugging lines of code and to work with user forms. There is also a standard tool bar which includes the