part0032

This is easily the most used feature of any text software or any other software that uses text. And although it is one of the most useful features as well, sadly, it can easily become annoying when it needs to be done for a long period of time. When you have to do something over and over again it becomes tedious and annoying, and it will likely prevent you from continuing your work without getting annoyed. This is why this is one of the most useful macros to have saved in your Excel work. And, there are different things that you will be looking to copy and paste in your work, so here are all the options of copy/cut and paste that are possible in Excel.

As its name suggests, this code copied info from one cell to another. You don’t need to know any kind of coding in order to use this as a macro, but you do need to make sure that you change the cell names to the ones that you will be using in your work.

Sometimes, you will want to copy an entire selection of what you are working on. Make sure that you change the cell range and name before using this code.

'Paste to a Defined Range

selection.copy range("b1")

'Offset Paste (offsets 2 cells down and 1 to the right

selection.offset(2,1).paste

Make your life easier by being able to copy an entire range of cells from one location to another. This is one of the most important and useful codes that you will use when you are working on a large work document that requires repetitive tasks.

For copying an entire column:

Sub PasteOneColumn()

This is an incredibly useful code because when you have to move between workbooks you also have to make many more clicks in the process. Going back and forth in order to copy something from one workbook to another wastes a lot of time and it also lowers your concentration at work drastically. Here is a useful code in how to do this with a single click. Just make sure that you change the names of the worksheets and the cell that you are copying to and from.

Sub Paste_Other_Sheet_or_Book()

When you finish your work for the day, instead of going from one workbook to another in order to save them, you can use a simple code to help you out in this process.

For Each wb In Application.Workbooks

As you work on and organize your Excel files, there will often come a time when you wish to save your work, or part of your work, as a new file. Instead of going through the entire process of saving your current work as a new file, this useful code helps you to complete the entire process with a single click:

workbook object .SaveAs(FileName, FileFormat, Password, WriteResPassword, _

ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, _

AddToMru,TextCodepage, TextVisualLayout, Local)

You may want to skip adding passwords to your work for the time being, because if you happen to get locked out of your work, it’s going to be quite the hassle to try and get back in. although passwords are certainly great for security, skip them until you are confident enough in your coding skills before you go back to using them again.

When you have hundreds of cells to work with, it is very difficult to organize them properly without the help of VBA. There is no way that you can efficiently scroll from one side of the worksheet to another searching for a value that you need to use in your work at that particular time. An example of such work is when you need to compare cell values. This simple code will help you to compare values of particular cells so that you don’t have to do it manually and waste a lot of time in the process.

If Range("A1").Value > Range("B1").Value Then

Elseif Range("A1").Value = Range("B1").Value Then

Looking for a maximum value in a column (without changing the way the column looks) is difficult because it means that you need to scroll your way through the numbers until you find what you believe to be the biggest one. Not only could you make a mistake in the process, but you could also lose a lot of precious time searching for something that can be achieved with a single click.

Function Max_Each_Column(Data_Range As Range) As Variant

You can display the results of this by suing the following subroutine:

Private Sub CommandButton1_Click()

Dim Answer As Variant

Dim No_of_Cols As Integer

No_of_Cols = Range("B5:G27").Columns.Count

ReDim Answer(No_of_Cols)

Answer = Max_Each_Column(Sheets("Sheet1").Range("B5:g27"))

For i = 1 To No_of_Cols

Many people use Excel to organize hundreds or thousands of cells of information. For example, some people use it to organize the names and email addresses of people that they could potentially contact for a project. Excel doesn’t automatically tell you if you have a duplicate cell or not, because it allows you to continue with your work as quietly as possible. However, you really wouldn’t want to contact someone who have already contacted and seem annoying. This super handy code will find the duplicates for you so that you can delete them before anything uncomfortable happens.

Sub Highlight_Duplicates(Values As Range)

For Each Cell In Values

This is a very handy way to organize your content by color without disturbing the way that the worksheet looks like at the moment. As you work on your Excel worksheet, you can decide to organize the content based on the color of the cell. This will help you a lot in the future, because there are actually a number of different codes that you can use to compile codes for different cell colors. This is an example of how to add up cells of a specific color.

Function Color_By_Numbers(Color_Range As Range, Color_Index As Integer) As Double

' Dim Color_By_Numbers As Double

'Will look at cells that are in the range and if

'the color interior property matches the cell color required

For Each Cell In Color_Range

If (Cell.Interior.ColorIndex = Color_Index) Then

Color_By_Numbers = Color_By_Numbers + Cell.Value

The above is very useful when you want to only focus on a single color. However, if you want to be super organized, you can give each topic of content in your worksheet a specific color as you go through it. There are a total of 56 different colors in Excel that you can choose from. An even more handy code is the one that calculates the sums for each color at the same time. This saves you time so that you don’t have to use the above code over and over again.

Private Sub CommandButton1_Click()

'Will look at each color and produce summary table of values

'on sheet 1 in cell A1 and downwards

Dim Current_Color_Number As Integer

Dim Color_Total As Double

For Current_Color_Number = 1 To 56

Color_Total = Color_By_Numbers(Sheets("Sheet2").Range("a11:aa64"), Current_Color_Number)

Worksheets("Sheet1").Range("A1").Offset(Current_Color_Number, 0).Interior.ColorIndex = Current_Color_Number

If Color_Total 0# Then

Next Current_Color_Number

This is another task that requires too much of your time and will likely make your work life more difficult unless you can find a way to simplify the process. Luckily, Excel has a code that makes the whole process far more easier for you. It will delete every row that meets the specific criteria which you have written in the code.

Sub Delete_Rows(Data_range As Range, Text As String)

Dim Row_Counter As Integer

For Row_Counter = Data_range.Rows.Count To 1 Step -1

If Data_range Is Nothing Then

If UCase(Left(Data_range.Cells(Row_Counter, 1).Value, Len(Text))) = UCase(Text) Then

Blank rows aren’t always a problem for people who work in Excel. However, if you reach a point where you need to send your worksheet to someone else, you want to make sure that it looks clean and professional. The following code will remove all blank rows, so that your workbook looks tighter and more professional.

Sub DeleteBlankRows()

This is the perfect code for people who use Excel all the time and who always start their work day with a new worksheet. This code is a simple way to make your life just that little bit easier first thing in the morning, ensuring that you don’t start your work day having to organize new worksheets. Instead, as soon as you login to the computer, you will already be ready to go and start working.

Private Sub Workbook_Open()

Dim New_Sheet_Name As String

New_Sheet_Name = Format(Now(), "dd-mm-yy")

If Sheet_Exists(New_Sheet_Name) = False Then

Dim Work_sheet As Worksheet

For Each Work_sheet In ThisWorkbook.Worksheets

Alternatively, you can also set the default worksheet that you want to start your day with, especially if it is always the same one.

Private Sub Workbook_Open()

There will be times when you need to be aware of all the files in a particular directory. Going through them manually one by one would cause you a lot of trouble and take up way too much of your time, which you could be using instead to create better work and reach your deadlines properly. This nifty code will help you to go through the process in time and to make sure that you can create a list of documents for yourself in an instant.

Sub List_All_The_Files_Within_Path()

Dim Row_No As Integer

Dim No_Of_Files As Integer

Dim File_Path As String

File_Path = "C:My Documents"

'Lists all the files in the current directory

.SearchSubFolders = False

Likewise, you can also use a similar code to list the files in a particular folder instead of in an entire directory. You may need this code for work, but you also use it to help organize the files in your computer or to share this information with other people without actually giving them access to any sensitive stuff in your computer system.

Dim oFolder As Object

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set oFolder = oFSO.GetFolder("C:\VBA Folder")

For Each oFile In oFolder.Files

You could do this manually by clicking on File and then selecting how you want something to be saved. However, it does take a bit of time to do this and it can easily become very difficult to do it when you have a long range that you need to deal with and organize. Here is a great code to help you overcome this problem and make your life that much easier.

Function ExportRange(WhatRange As Range, _

Dim HoldRow As Long 'test for new row variable

Dim c As Range 'loop through range variable

For Each c In WhatRange

ExportRange = Left(ExportRange, Len(ExportRange) - 1) _

'Trim extra delimiter

ExportRange = Left(ExportRange, Len(ExportRange) - 1)

'Kill the file if it already exists

If Len(Dir(Where)) > 0 Then

Open Where For Append As #1 'write the new file

Print #1, ExportRange

Hyperlinks are also some of the most useful and most often used functions in Excel, especially if you use a worksheet to collect a variety of data. Hyperlinks help you to quickly go to a link either inside your computer system or perhaps somewhere else on the Internet. Luckily, macros allow you to complete every action that you can possibly think of using hyperlinks. We’ll go through all of the options in order to give you the best possible outline of how to use VBA to create brilliant tasks with the help of macros. This will save you a lot of time and make your work life that much easier.

If you just want to add a hyperlink code to a particular cell or collection of cells, use this code:

Sub AddHyperlinkToCell()

ActiveSheet.Hyperlinks.Add Range("A1"), Address:="https://www.example.com/"

If you want to be a little more specific with your link and how it displays, you can easily add text to the hyperlink which will tell you exactly where the link is going to take you. This is code that you will be using in this particular situation:

Sub TextToDisplayForHyperlink()

ActiveSheet.Hyperlinks.Add Range("A1"), Address:="https://www.example.com/", TextToDisplay:="Example"

If you want to be even more organized, or if perhaps the text of your hyperlinks becomes repetitive after a while, you can easily use VBA to add a floating text to your hyperlink. When someone hovers over the link, the text will pop up, whose purpose is to give the viewer more information about what hides behind the hyperlink. This is very useful when you want to organize even more content in your worksheet.

Sub ScreenTipForHyperlink()

ActiveSheet.Hyperlinks.Add Range("A1"), Address:="https://www.example.com/", TextToDisplay:="Example", ScreenTip:="This is the link for Example"

This may not be something that you will be using very often, but there might come a time when you want to remove all hyperlinks from a worksheet. If you were to do this manually it would take you a very long amount of time to complete this process. To delete all hyperlinks with a single code, use this:

Sub RemoveAllHyperlinksInASheet()

ThisWorkbook.Sheets(1).Hyperlinks.Delete

This is very useful when you are about to open a number of different links but you want each one to open in a separate tab. This is very useful for ensuring that you can open multiple links without disturbing the initial link that you were about to open. This will allow you to stay organized and prepared for work whenever you need it. This is the code that you will use to complete this task:

Sub FollowHyperlinkForWebsite()

ActiveWorkbook.FollowHyperlink Address:="https://www.example.com", NewWindow:=True

Like we mentioned before, hyperlinks also allow you to link to sections of your computer. This is also great for organization and will make it a lot easier for you to reach the files that you need to open frequently, or the files and folders that you always need for work. Although this particular link will not be useful to people who are not on your computer, you can make this link useful by making it connect to a file on a shared drive so that everyone can see it if needed. Use this code to do so:

Sub FollowHyperlinkForFolderOnDrive()

ActiveWorkbook.FollowHyperlink Address:="C:\Desktop\ExcelFiles"

Anything that makes you go back and forth between sheets or workbooks while you work is truly a waste of time on many levels. You never want to do these repetitive tasks if you don’t need to, especially because they will easily make you feel irritated and unwilling to continue with your work. To avoid this problem, you can use this simple code which will make your life a lot easier:

ActiveSheet.Hyperlinks.Add Range("A1"), Address:="", SubAddress:="'" & Sheet2.Name & "'!B2", TextToDisplay:="Click Here to Go to Sheet2, cell B2 of the same workbook"

Even though hyperlinks will be a different color, they are still very difficult to find if there are many of them in a single worksheet. There may be times when you will want to organize all the hyperlinks that you’ve added into the worksheet, and it will be a lot easier to do so if the hyperlinks are clearly visible. To do this, use this code:

Sub ShowAllTheHyperlinksInTheWorksheet()

Set ws = ThisWorkbook.Sheets(1)

For Each lnk In ws.Hyperlinks

Debug.Print lnk.Address

This is a very useful code to help you find the particular values that you would like to work with at a particular moment. Instead of having to manually go through the entire sheet to find larger values, something which could take hours if you have hundreds of values insert, this code will show you exactly which values you are looking for. Just make sure to enter the value that you are looking for.

Sub HighlightGreaterThanValues()

i = InputBox("Enter Greater Than Value", "Enter Value")

Selection.FormatConditions.Delete

Selection.FormatConditions.Add Type:=xlCellValue,

Operator:=xlGreater, Formula1:=i

Selection.FormatConditions(Selection.FormatConditions.Count).S

With Selection.FormatConditions(1)

.Font.Color = RGB(0, 0, 0)

.Interior.Color = RGB(31, 218, 154)

This is of course the opposite option than the one we have mentioned above.

Sub HighlightLowerThanValues()

i = InputBox("Enter Lower Than Value", "Enter Value")

Selection.FormatConditions.Delete

Selection.FormatConditions.Add Type:=xlCellValue,

Operator:=xlLower, Formula1:=i

Selection.FormatConditions(Selection.FormatConditions.Count).S

With Selection.FormatConditions(1)

.Font.Color = RGB(0, 0, 0)

.Interior.Color = RGB(217, 83, 79)

In case you need to look for numbers which have a negative value, use this code to help you out:

Sub highlightNegativeNumbers()

For Each Rng In Selection

If WorksheetFunction.IsNumber(Rng) Then

If Rng.Value < 0 Then

Rng.Font.Color= -16776961

There will be times when you will look for a particular section of text in your worksheet. Perhaps you would like to check if a section of text exists, or maybe you would like to edit it to say something else. The fastest way to do this is to use a code which will help you to find exactly what you are looking for:

Sub highlightValue()

Dim myChar As String

On Error Resume Next

If ActiveWindow.RangeSelection.Count> 1 Then

myTxt= ActiveWindow.RangeSelection.AddressLocal

myTxt= ActiveSheet.UsedRange.AddressLocal

LInput: Set myRg= Application.InputBox("please select the data

range:", "Selection Required", myTxt, , , , , 8)

If myRg Is Nothing Then

If myRg.Areas.Count > 1 Then

MsgBox"not support multiple columns" GoToLInput

If myRg.Columns.Count <> 2 Then

MsgBox"the selected range can only contain two columns "

For I = 0 To myRg.Rows.Count-1

myStr= myRg.Range("B1").Offset(I, 0).Value

With myRg.Range("A1").Offset(I, 0)

For J = 1 To Len(.Text)

Mid(.Text, J, Len(myStr)) = myStrThen

.Characters(J, Len(myStr)).Font.ColorIndex= 3

This is a very useful code! It helps you to quickly find the cells that have a spelling mistake in them. It makes it very easy to spot the areas that you may need to work on a little bit more. But not only that, this code also helps you find words in another language which you may have used throughout the worksheet.

Sub HighlightMisspelledCells()

For Each rng In ActiveSheet.UsedRange

If Not Application.CheckSpelling(word:=rng.Text) Then

rng.Style= "Bad" End If

There are times when an error occurs in a cell without you even knowing. Sometimes this is because you have not entered a code properly, but other times it could just be due to the click of your mouse which you do not realize that you’ve made. In either case, errors in cells make it very difficult for Excel to correctly calculate everything in the worksheet. This means that you might end up relying on data which is not accurate, and depending on how serious your job is, this could also become a serious problem. This is the code that will help you to quickly go through your worksheet and make sure that you are able to fix all of the errors that need fixing.

Sub highlightErrors()

For Each rng In ActiveSheet.UsedRange

If WorksheetFunction.IsError(rng) Then

i = i + 1 rng.Style = "bad"

MsgBox "There are total " & i & " error(s) in this worksheet."

Sometimes, you may need to look for cells which have a specific word in them. This happens when you need to organize the spreadsheet in a way that does not allow you to see these particular variables visibly. You cannot possibly organize a worksheet to show you every single cell at the same time, especially if you have many cells that you need to work on. This is exactly where VBA comes in to help you find the things that need finding as quickly as possible. Hopefully even with a single click.

Sub highlightSpecificValues()

c = InputBox("Enter Value To Highlight")

For Each rng In ActiveSheet.UsedRange

MsgBox "There are total " & i &" "& c & " in this worksheet."

This is a great code to help you find anomalies in your worksheet. This doesn’t necessarily mean that the cells are incorrect, it simply means that you will use this code when you are looking for values that are out of the ordinary. You will need to choose the selection of the worksheet in which you will be looking for these values. You could potentially look for them throughout the entire worksheet, although this might bring back too many variables for you to go through so it is better to look for them through a selection.

Sub highlightUniqueValues()

rng.FormatConditions.Delete

Dim uv As UniqueValues

Set uv = rng.FormatConditions.AddUniqueValues

uv.DupeUnique = xlUnique

uv.Interior.Color = vbGreen

This is a great code to use when you also have to print pages from your worksheet. Especially if you are often printing particular pages with particular settings needed from the worksheet. Once you set this code up the way you needed it to look like, you will never have to go through the whole process of setting up printing again. Simply click on the VBA button (more about that below) and print the pages that you need in no time.

Sub printCustomSelection()

Dim startpageAs Integer

Dim endpageAs Integer

startpage= InputBox("Please Enter Start Page number.", "Enter

If Not WorksheetFunction.IsNumber(startpage) Then

MsgBox"Invalid Start Page number. Please try again.", "Error"

endpage= InputBox("Please Enter End Page number.", "Enter

If Not WorksheetFunction.IsNumber(endpage) Then

MsgBox"Invalid End Page number. Please try again.", "Error"

Selection.PrintOutFrom:=startpage, To:=endpage, Copies:=1,

This is a very handy code that will help you to organize worksheets by their name with a simple click. Although you might be a very organized person, perhaps there will be times when you will receive a worksheet from someone else who has not taken the time to organize their work. Instead of wasting time making things more logical for you, you can simply use this code to get everything ready for you to start working on the worksheet you need right away.

Sub SortWorksheets()

Dim iAnswer As VbMsgBoxResult

iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _

& "Clicking No will sort in Descending Order", _

vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort

For i = 1 To Sheets.Count

For j = 1 To Sheets.Count - 1

If iAnswer = vbYes Then

If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then

Sheets(j).Move After:=Sheets(j + 1)

ElseIf iAnswer = vbNo Then

If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then

Sheets(j).Move After:=Sheets(j + 1)

Here’s another great quick code to help make your work life easier. If you need to send a particular workbook via email, this is the code that will complete this process automatically for you. However, this particular code is only for the worksheet that is currently active on your screen. But this is fine because it is assumed that you will be sending the worksheet that you are working on at the moment.

Dim OutApp As Object

Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")

Set OutMail = OutApp.CreateItem(0)

.to = "example@example.com"

.Subject = " Report"

.Body = "Hell, Please find attached Report."

.Attachments.Add ActiveWorkbook.FullName

Set OutMail = Nothing

Set OutApp = Nothing

This is a great way to create a PDF document that will specifically look at a particular part of the worksheet. You would not be able to do this another way, because it would require you to first remove the section that you would like to turn into a PDF in a Word document and then continue your work from there. This is obviously a much slower way to complete your work, which is why this code will help you complete this task without having to leave Excel.

Dim pt As PivotTable

Dim pf As PivotField

On Error Resume Next

Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.n ame)

If pt Is Nothing Then

MsgBox "Example text."

For Each pf In pt.PivotFields

pf.Subtotals(1) = True

pf.Subtotals(1) = False

This is a great code either for yourself or for someone who you are working with on this particular project. Instead of getting lost in mountains of content, you can quickly create a table of contents with a simple code, which will help everyone have an easier time getting to know the content in the worksheet. If you were to develop something like this manually, it would literally take you hours to complete the task. Especially if there are many cells of content that you need to go through.

Sub TableofContent()

On Error Resume Next

Application.DisplayAlerts = False

Worksheets("Table of Content").Delete

Application.DisplayAlerts = True

ThisWorkbook.Sheets.Add Before:=ThisWorkbook.Worksheets(1)

ActiveSheet.Name = "Table of Content"

For i = 1 To Sheets.Count

Anchor:=ActiveSheet.Cells(i, 1), _

SubAddress:="'" & Sheets(i).Name & "'!A1", _

ScreenTip:=Sheets(i).Name, _

TextToDisplay:=Sheets(i).Name

Did you know that VBA can also help you to search for information on the internet without having to leave Excel? This saves you an even greater amount of time, because you don’t have to constantly switch from one window to another. This code will do all of the difficult tasks for you and it will then come back with the Google information that you were searching for.

Sub SearchWindow32()

Dim chromePath As String

Dim search_string As String

query = InputBox("Enter here your search here", "Google Search")

search_string = query

search_string = Replace(search_string, " ", "+")

'Uncomment the following line for Windows 64 versions and comment out Windows 32 versions'

chromePath = "C:Program

FilesGoogleChromeApplicationchrome.exe"

'Uncomment the following line for Windows 32 versions and comment out Windows 64 versions

chromePath = "C:Program Files

(x86)GoogleChromeApplicationchrome.exe"

Shell (chromePath & " -url http://google.com/#q=" & search_string)

This code helps you go through and complete one of the most annoying things that you would ever have to do in a worksheet. Not only is it incredibly difficult to find extra spaces just by looking at hundreds and hundreds of cells, but an extra space can also really ruin the visual display of the overall worksheet. This excellent code will help you find extra spaces instantly by highlighting them, after which you can decide whether or not you need and want to remove this extra space or if you want to keep them instead.

Dim myRange As Range

Select Case MsgBox("You Can't Undo This Action. " & "Save

vbYesNoCancel, "Alert")

Case Is = vbYesThisWorkbook.Save

Set myRange = Selection

For Each myCell In myRange

If Not IsEmpty(myCell) Then

myCell = Trim(myCell)

Imagine if, in a worksheet with hundreds or thousands of cells, you suddenly need to change all of the dates into days of the week. Doing this manually would likely drive you crazy and would take days to complete. There would be no way of speeding this process up manually because you would still have to change each cell individually. Luckily, this code will complete the entire process for you with a single click, making your life that much easier.

Dim tempCell As Range

Selection.Value = Selection.Value

For Each tempCell In Selection

If IsDate(tempCell) = True Then

.Value = Day(tempCell)

Although this is perhaps not something that you would often have to do in Excel, since the software isn’t exclusively for text documents, this code will help you complete the process and count the number of words properly.