You are currently viewing 50 useful Macro Codes for Excel

50 useful Macro Codes for Excel

Macros code is visual basic for applications (VBA). Macros can be used in excel to automate tasks that are performed regularly and prevent you from manually performing them each and every time. The automation process by the use of macros in excel results in time-saving and produces quality, reliable work. You can create macros in excel by recording the steps of what you want to perform or writing them in person. Writing macros code yourself is advantageous since it gives you a greater scope of control over macros. By using macro, you can perform a wide range of tasks from simple formatting steps to complex export of excel sheets.

 

How to Run the Macros

1. Go to the Developer tab.

2. Click on Macros.

3. In the dialog box, select the macro you want to run.

4. Click on the Run button.

Below is a list of Macro codes to enable you to get started

1. Insert multiple columns

This code gives you the option of inserting multiple columns. When it is run, you will feed the number of columns you are interested in, then click ok. The columns will be added after the selected cell.

Sub InsertMultipleColumns()
Dim k As Integer
Dim l As Integer

ActiveCell.EntireColumn.Select

On Error GoTo Last

i = InputBox("Enter number of columns to insert", "Insert Columns")

For l = 1 To i

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightorAbove

Next l

Last: Exit Sub

End Sub

To add the column before the selected cell, replace the code xIToRight with xIToLeft.

2. Autofit columns

This code will instantly Autofit all the columns in your Worksheet when you Run it.

Sub AutoFitColumns()

Cells.Select

Cells.EntireColumn.AutoFit

End Sub

3. Remove Text wrap

Remove text wrap code will help remove text wrap in Worksheet by selecting all columns and removing text wrap.

Sub RemoveTextWrap()
Range("A1").WrapText = False
End Sub

4. Add header

This code has the ability to add header when run.

Sub AddCustomHeader()
Dim inputText As String
inputText = InputBox("Enter your text here", "Custom Header")

'Add custom text to the center header

With ActiveSheet.PageSetup

.LeftHeader = ""

.CenterHeader = inputText

.RightHeader = ""

End With

End Sub

To put a header or a footer date you can alternatively use this code;

Sub DateInHeader()

With ActiveSheet.PageSetup

.LeftHeader = ""

.CenterHeader = "&D"

.RightHeader = ""

.LeftFooter = ""

.CenterFooter = ""

.RightFooter = ""

End With

End Sub

This macro simply uses the tag "&D" for adding the date. The header can be changed to the footer or side by replacing the " with the "&D." you can also add a specific date or desired header or footer by replacing the "&D" with your preference.

5. Highlight the active rows and columns

This code is resourceful when you want to analyze a data table.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim strRange As String
strRange = Target.Cells.Address & "," & _

Target.Cells.EntireColumn.Address & "," & _

Target.Cells.EntireRow.Address

Range(strRange). Select

End Sub

A quick step to apply the code is to open VBE(ALT + F11), then go to project explorer, select your Workbook and double click the particular Worksheet to activate the macro, paste the code into it, and from the event drop menu, select "BeforeDoubleClick," it's all done close VBE.

6. Highlight named ranges

To ascertain how many names you have in your Worksheet, this code can help you highlight all of them.

Sub HighlightRanges()
Dim RangeName As Name
Dim HighlightRange As Range

On Error Resume Next

For Each RangeName In ActiveWorkbook.Names

Set HighlightRange = RangeName.RefersToRange

HighlightRange.Interior.ColorIndex = 36

Next RangeName

End Sub

7. Highlight negative numbers

Sometimes your Worksheet may be big, and you may want to only get negative numbers. Macro code has you covered. This code will check every cell from the Range and select those cells with negative numbers.

Sub highlightNegativeNumbers()
Dim Rng As Range
For Each Rng In Selection

If WorksheetFunction.IsNumber(Rng) Then

If Rng.Value < 0 Then

Rng.Font.Color= -16776961

End If

End If

Next

End Sub

8. Print narrow margin

When this macro code is run, it gives you the liberty to print a narrow margin depending on your set desire.

Sub printNarrowMargin()
With ActiveSheet.PageSetup
.LeftMargin = Application

.InchesToPoints (0.25)

.RightMargin = Application.InchesToPoints(0.25)

.TopMargin = Application.InchesToPoints(0.75)

.BottomMargin = Application.InchesToPoints(0.75)

.HeaderMargin = Application.InchesToPoints(0.3)

.FooterMargin = Application.InchesToPoints(0.3)

End With

ActiveWindow.SelectedSheets.PrintOut _

Copies:=1, _

Collate:=True, _

IgnorePrintAreas:=False

End Sub

9. Unhide all hidden worksheets

By using this code, it enables you to unhide all hidden Worksheets.

Sub UnhideAllWorksheets()
Dim WS As Worksheet
'Loop through all Worksheet and set them to visible

For Each ws In

ActiveWorkbook.Worksheets

ws.Visible = xlSheetVisible

Next ws

End Sub

10. Resize all charts in a worksheet

If you have many charts and you want them to be organized to a consistent size, a macro can be of aid just by a single click.

'the width and height can be adjusted as desired by changing the values below.

Sub Resize_Charts()
Dim counter As Integer
For counter = 1 To ActiveSheet.ChartObjects.Count

'change the height and width to the size required

With ActiveSheet.ChartObjects(counter)

.Width = 500

.Height = 300

End With

Next counter

End Sub

11. Disable page break

Worksheets have page breaks, and you may at some point not prefer this. You can use this simple code to disable page breaks.

Sub DisablePageBreaks()
Dim wb As Workbook
Dim wks As Worksheet

Application.ScreenUpdating = False

For Each wb In Application.Workbooks

For Each Sht In wb.Worksheets

Sht.DisplayPageBreaks = False

Next Sht

Next wb

Application.ScreenUpdating = True

End Sub

12. Close all workbooks at once

Before closing, the macro code will first check all the workbooks one by one and have them closed. For those that are not saved, a message will pop up, alerting you whether to save it or not.

Sub CloseAllWorkbooks()
Dim wbs As Workbook
For Each wbs In Workbooks

wbs.Close SaveChanges:=True

Next wb

End Sub

13. Refresh all pivot tables.

It is of essence to refresh your pivot tables when you are updating your data. Sometimes, you may forget to do so, but excel macros have made it easy by using this code.

Sub vba_referesh_all_pivots()
Dim pt As PivotTable
'With just one loop, refresh all pivot tables!

For Each pt In ActiveWorkbook.PivotTables

pt.RefreshTable

Next pt

End Sub

2. Create a table of content

Anyone who has dealt with excel for a long time can testify that scrolling through a Worksheet that is voluminous is hectic and time-consuming. We can create a table of content using macros to make maneuvering easy for us. Just use this code. It will be fun.

Sub TableofContent()
Dim i As Long
On Error Resume Next

Application.DisplayAlerts = False

Worksheets("Table of Content").Delete

Application.DisplayAlerts = True

On Error GoTo 0

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

ActiveSheet.Name = "Table of Content"

For i = 1 To Sheets.Count

With ActiveSheet

.Hyperlinks.Add _

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

Address:="", _

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

ScreenTip:=Sheets(i).Name, _

TextToDisplay:=Sheets(i).Name

End With

Next i

End Sub

15. Convert Range into an image

Sub PasteAsPicture()
Application.CutCopyMode = False
Selection.Copy

ActiveSheet.Pictures.Paste.Select

End Sub

16. Activate data entry form

Sub DataForm()

'Show the default data entry form

ActiveSheet.ShowDataForm

End Sub

17. Convert all formulas into values

When you need to use a lot of volatile heavy functions or send a workbook free from all the sketchy and complex formulas- only the result of the calculations rather than the full excel model, it is essential that you convert all formulas to values. Macros help you by using this simple code.

Sub convertToValues()
Dim MyRange As Range
Dim MyCell As Range

Select Case _

MsgBox("You Can't Undo This Action. " _

& "Save Workbook First?", vbYesNoCancel, _

"Alert")

Case Is = vbYes

ThisWorkbook.Save

Case Is = vbCancel

Exit Sub

End Select

Set MyRange = Selection

For Each MyCell In MyRange

If MyCell.HasFormula Then

MyCell.Formula = MyCell.Value

End If

Next MyCell

End Sub

18. Reverse Text

Reverse Text is not included in excel since it is not in use or let's say rarely used. But those who would want to use it to generate special code, for fun or need to see if a string is a palindrome may use the code below. This code has the ability to display Text backward- it actually converts the Text and puts it in reverse.

Public Function ReverseText(ByVal cell As Range) As String
'Reverse text using this function
ReverseText = VBA.strReverse(cell.Value)

End Function

'Make sure you have a selection ready

Sub ReverseTextInSelection()

Dim range As Range

Selection.Value= Selection.Value

'Loop through all the cells

For Each Range In Selection

'Call your function

range= ReverseText(range)

Next Range

End Sub

19. Activate A1 reference style

Sub ActivateA1()
If Application.ReferenceStyle = xlR1C1 Then
Application.ReferenceStyle = xlA1

Else

Application.ReferenceStyle = xlA1

End If

End Sub

20. Convert a date into years

Sub date2year()
Dim tempCell As Range
Selection.Value = Selection.Value

For Each tempCell In Selection

If IsDate(tempCell) = True Then

With tempCell

.Value = Year(tempCell)

.NumberFormat = "0"

End With

End If

Next tempCell

End Sub

21. Word count from entire Worksheet

It is possible that as we work on an excel workbook, we may be interested in getting the word count. Unfortunately, Excel does not have an inbuilt word counter, but luckily we may use Macros code to resolve this problem. In relation to other ways that we may use, Macros has proven to be simple to use. The code below will do the word count for you.

Sub Word_Count_Worksheet()
Dim WordCnt As Long
Dim rng As Range

Dim S As String

Dim N As Long

For Each rng In ActiveSheet.UsedRange.Cells

S = Application.WorksheetFunction.Trim(rng.Text)

N = 0

If S <> vbNullString Then

N = Len(S) – Len(Replace(S,",")) + 1

End If

WordCnt = WordCnt + N

Next rng

MsgBox "There are total " _

& Format(WordCnt, "#,##0") & _

" words in the active worksheet"

End Sub

22. Remove decimals from numbers

Sub removeDecimals()
Dim lnumber As Double
Dim lResult As Long

Dim rng As Range

For Each rng In Selection

rng.Value = Int(rng)

rng.NumberFormat = "0"

Next rng

End Sub

23. Add a number in all the numbers

Sub addNumber()
Dim rng As Range
Dim i As Integer

i = InputBox("Enter number to multiple", "Input Required")

For Each rng In Selection

If WorksheetFunction.IsNumber(rng) Then

rng.Value = rng + i

Else

End If

Next rng

End Sub

Alternatively, you may use this as an option.

Sub InsertNumbers()
Dim maxNumber As Integer
Dim counter As Integer

On Error GoTo Last

maxNumber = InputBox("Enter the Max Value", "Generate 1 to n")

'Generate all the numbers

For counter = 1 To maxNumber

ActiveCell.Value = counter

'Move one cell below

ActiveCell.Offset(1, 0).Activate

Next counter

Last: Exit Sub

End Sub

24. Replace blank cells with zeros

Sub replaceBlankWithZero()

Dim rng As Range

Selection.Value = Selection.Value

For Each rng In Selection

If rng = "" Or rng = " " Then

rng.Value = "0"

Else

End If

Next rng

End Sub

25. Close all workbooks at once

This process takes place in two steps; the first being declaring an object variable to represent a workbook project, the second loop all the open Workbook, finalize by saving and closing them.

Sub CloseAllWorkbooks()
Dim wbs As Workbook
'Loop through all workbooks and close them

For Each wbs In Workbooks

wbs.Close SaveChanges:=True

Next wb

End Sub

If you don't want to save, change the SaveChange argument to false.

26. Save each Worksheet as a separate PDF

This code will help you create PDFs from worksheets.

Sub SaveEachWorksheetAsPdfFile()
Dim Worksheet As Worksheet
'Loop through all of the worksheets
'Remember to change the folder URL as well
For Each worksheet In Worksheets
worksheet.ExportAsFixedFormat xlTypePDF, 
"C:\ChangeMe\" & worksheet.Name & ".pdf" 
Next worksheet
End Sub

27. Protect all worksheets instantly

It is possible to protect all your Worksheets. You will need to ask the user for the password then it will be used to protect the worksheets.

Sub ProtectAllWorksheets()

Dim Worksheet As Worksheet

Dim pword As String

'Get the user's password

pword = InputBox("Enter a Password to secure your worksheets", "Password")

'Loop through the worksheets toprotect all of them

For Each Worksheet In ActiveWorkbook.Worksheets

worksheet.Protect Password:=pword

Next Worksheet

End Sub

This code will simply enable you protect the Worksheet with the user-given password.

28. Automatically insert date and time stamp in the adjacent cells

To use this code, right-click the worksheet tab you intend to use and choose the view code from the appearing menu >> use the code below and save it. This will automatically put a new timestamp. Depending on your workbook preference Columns A and B can be changed, and the format of mm/dd/yy hh:mm:ss to what you desire.

Private
Sub
Worksheet_Change(ByVal
Target As
Range)
'UpdatebyKutools20190919
Dim
xRInt As
Integer

Dim
xDStr As
String

Dim
xFStr As
String

On
Error
Resume
Next

xDStr = "A"
'Data Column

xFStr = "B"
'Timstamp Column

If
(Not
Application.Intersect(Me.Range(xDStr & ":"
& xDStr), Target) Is
Nothing) Then

       xRInt = Target.Row

       Me.Range(xFStr & xRInt) = Format(Now(), "mm/dd/yyyy hh:mm:ss")

End
If

End
Sub

29. Highlight blank cells

It is sometimes difficult to identify cells that are black, especially those containing a single space. This code may ease it up for you by checking them out and highlighting them.

Sub blankWithSpace()
Dim rng As Range
For Each rng In ActiveSheet.UsedRange

If rng.Value = " " Then

rng.Style = "Note"

End If

Next rng

End Sub

30. Show a welcome message

'Ensure this is saved inside the Workbook
Sub Workbook_Open()
MsgBox "Enjoy as you learn more and enhance your skills in excel."
End Sub

This code will allow you to have a starting message in excel.

31. Highlight cells with comments

Sub HighlightCellsWithComments()
'Select the cells with comments.
Selection.SpecialCells(xlCellTypeComments).Select
'Set all the cells' style to the Note style
Selection.Style= "Note"
End Sub

32. Highlight unique values

Sometimes it may be needful for you to find a list of unique values in excel. This code has simplified it all for you.

Sub HighlightUniqueValuesInSelection()
'Set the Range as the current selection
Dim Range As Range
Set range = Selection
range.FormatConditions.Delete    
'Color the unique values with green
Dim uniqueVals As UniqueValues
Set uniqueVals = range.FormatConditions.AddUniqueValues
uniqueVals.DupeUnique = xlUnique
uniqueVals.Interior.Color = RGB(0, 255, 0)
End Sub

33. Print comments

If you are interested in printing only the comments, use this code.

Sub PrintCommentsToLastPage()
'Print all comments to the last page
With ActiveSheet.PageSetup
.printComments= xlPrintSheetEnd
End With
End Sub

34. Insert multiple worksheets

Sub InsertMultipleWorksheets()
Dim numOfSheets As Integer
'Get number of sheets from the user
numOfSheets = InputBox("Enter number of sheets to insert", "Enter number of sheets")
'Add the additional sheets after the currently active sheet
Sheets.Add After:=ActiveSheet, Count:=numOfSheets
End Sub

35. Delete all blank sheets

After finishing your work, there might be a necessity to clean up and remove the unnecessary blank sheets that were not used. This process can be automated using this code.

Sub DeleteBlankWorksheets()
Dim wsheet As Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'Loop through all worksheets and delete the blank ones
For Each sheet In Application.Worksheets
If Application.WorksheetFunction.CountA(wsheet.UsedRange) = 0 Then
wsheet.Delete
End If
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

36. Add a workbook to a normal attachment

If you are done editing your Workbook and now ready to send it via email, the process is already automated by running this code.

Sub AttachWorkbookIntoEmailMessage()

Dim OutlookApp As Object

Dim OutlookMail As Object

Set OutlookApp = CreateObject("Outlook.Application")

Set OutlookMail = OutlookApp.CreateItem(0)

'Let's create an email message and display it

'Remember to change the parameters below

With OutlookMail

.To = "support@myexcelonline.com"

.Subject = "Have a look at this workbook."

.Body = "Hey John, Could you help out on this?"

.Attachments.Add ActiveWorkbook.FullName

.Display

End With

Set OutlookMail = Nothing

Set OutlookApp = Nothing

End Sub

37. Auto-update pivot table range

Sub UpdatePivotTableRange()
Dim Data_Sheet As Worksheet
Dim Pivot_Sheet As Worksheet

Dim StartPoint As Range

Dim DataRange As Range

Dim PivotName As String

Dim NewRange As String

Dim LastCol As Long

Dim lastRow As Long

'Set Pivot Table & Source Worksheet

Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3")

Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3")

'Enter in Pivot Table Name

PivotName = "PivotTable2"

'Defining Staring Point & Dynamic Range

Data_Sheet.Activate

Set StartPoint = Data_Sheet.Range("A1")

LastCol = StartPoint.End(xlToRight).Column

DownCell = StartPoint.End(xlDown).Row

Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))

NewRange = Data_Sheet.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1)

'Change Pivot Table Data Source Range Address

Pivot_Sheet.PivotTables(PivotName). _

ChangePivotCache ActiveWorkbook. _

PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange)

'Ensure Pivot Table is Refreshed

Pivot_Sheet.PivotTables(PivotName).RefreshTable

'Complete Message

Pivot_Sheet.Activate

MsgBox "Your Pivot Table is now updated."

End Sub

38. Paste chart as an image

Depending on preference, sometimes you may need your chart as an image. This code enables you to convert your chart to an image. Use this code.

Sub ConvertChartToPicture()
ActiveChart.ChartArea.Copy
ActiveSheet.Range("A1").Select

ActiveSheet.Pictures.Paste.Select

End Sub

39. Insert a linked picture

The linked image is a feature in Excel that you can use to create dashboards wherein images can be resized and linked to the actual report. The code can aid in creating your own linked image.

'Make sure you have first a selected range.
Sub InsertALinkedImage()
Selection.Copy
'Paste the selection as an image
ActiveSheet.Pictures.Paste(Link:=True).Select
End Sub

40. Remove characters from a string

You can remove a character from starting of a text string by referring to a cell. You can alternatively insert a text into the function number of characters to remove from a text string. Two arguments are in consideration. "rng" for the text string and "cnt" for character count.

To remove the first character of a cell, enter 1 in cnt.

Public Function removeFirstC(rng As String, cnt As Long)
removeFirstC = Right(rng,Len(rng) – cnt)
End

41. Remove time and date

This code will help you delete rows based on the date.

'

If Format(Cells(iCntr, 1), "dd-mm-yyyy") = Format(Now(), "dd-mm-yyyy") Then

Rows(iCntr).Delete

End If

Starting program and sub procedure to write VBA code to delete rows based on date.
Sub sbDelete_Rows_Based_On_Date()
'Declaring the variable lRow as long to store the last row number

Dim lRow As Long

'Declaring the variable iCntr as long to use in the For loop

Dim iCntr As Long

'Assigning the last row value to the variable lRow

lRow = 20

'Using for loop

'We are checking the each cell value if the cell value equals today's date.

'And deleting the row if true

For iCntr = lRow To 1 Step -1

42. Convert to upper case

Sub convertUpperCase()

Dim Rng As Range

For Each Rng In Selection

If Application.WorksheetFunction.IsText(Rng) Then

Rng.Value = UCase(Rng)

End If

Next

End Sub

This code will check the Range of selected cells and convert it into uppercase text when run.

43. Macor codeRemoving a character from selection in excel

Sub removeChar()

Dim Rng As Range

Dim rc As String

rc = InputBox("characters", "Enter your Value")

For Each Rng In Selection

Selection.Replace What:=rc, Replacement:=""

Next

End Sub

Particular characters may be removed from a selected cell by using this code. An input box will display where you will enter the character you intend to remove.

44. Add A-Z Alphabets in a range

Sub addsAlphabets1()
Dim i As Integer
For i = 65 To 90

ActiveCell.Value = Chr(i)

ActiveCell.Offset(1, 0).Select

Next i

End Sub

Sub addsAlphabets2()

Dim i As Integer

For i = 97 To 122

ActiveCell.Value = Chr(i)

ActiveCell.Offset(1, 0).Select

Next i

End Sub

45. Hide pivot table subtotals

Modifying pivot tables is one of the many functionalities entailed in Excel Macros. We can use macros modifying feature that hides pivot table subtotal using this code.

'Select a cell first from your pivot table

Sub HidePivotTableSubtotals()

Dim pTable As PivotTable

Dim pField As PivotField

On Error Resume Next

'Get the pivot table first

Set pTable = ActiveSheet.PivotTables(ActiveCell.PivotTable.name)

'Check if a pivot table is found

If pTable Is Nothing Then

MsgBox "Please select a cell first from your Pivot Table."

Exit Sub

End If

'For each subtotal, make it hidden

For Each pField In pTable.PivotFields

pField.Subtotals(1) = True

pField.Subtotals(1) = False

Next pField

End Sub

46. Create a pivot table

To create a pivot table, eight steps are important.

1. Variable Declaration- you need first to declare the variables needed in our coded to define different things

'Declare Variables

Dim PSheet As Worksheet (create a sheet for the pivot table)

Dim DSheet As Worksheet (use as datasheet)

Dim PCache As PivotCache (use as name for pivot table cache) Dim PTable As PivotTable(use as name for pivot table)

Dim PRange As Range(define a source of data range)

Dim LastRow As Long

Dim LastCol As Long

2. Insert a new worksheet

3. Define the Range of data

4. The next thing is to create a pivot cache

5. Insert a black pivot table

6. Add rows and columns

7. Put data fields

8. Lastly format pivot table

The code summary of the steps is simplified below and can be used to create a pivot table.

Sub InsertPivotTable()
'Macro By ExcelChamps
'Declare Variables

Dim PSheet As Worksheet

Dim DSheet As Worksheet

Dim PCache As PivotCache

Dim PTable As PivotTable

Dim PRange As Range

Dim LastRow As Long

Dim LastCol As Long

'Insert a New Blank Worksheet

On Error Resume Next

Application.DisplayAlerts = False

Worksheets("PivotTable").Delete

Sheets.Add Before:=ActiveSheet

ActiveSheet.Name = "PivotTable"

Application.DisplayAlerts = True

Set PSheet = Worksheets("PivotTable")

Set DSheet = Worksheets("Data")

'Define Data Range

LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row

LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column

Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

'Define Pivot Cache

Set PCache = ActiveWorkbook.PivotCaches.Create _

(SourceType:=xlDatabase, SourceData:=PRange). _

CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _

TableName:="SalesPivotTable")

'Insert Blank Pivot Table

Set PTable = PCache.CreatePivotTable _

(TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")

'Insert Row Fields

With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year")

.Orientation = xlRowField

.Position = 1

End With

With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month")

.Orientation = xlRowField

.Position = 2

End With

'Insert Column Fields

With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone")

.Orientation = xlColumnField

.Position = 1

End With

'Insert Data Field

With ActiveSheet.PivotTables("SalesPivotTable")

.PivotFields ("Amount")

.Orientation = xlDataField

.Function = xlSum

.NumberFormat = "#,##0"

.Name = "Revenue "

End With

'Format Pivot Table

ActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True

ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"

End Sub

47. Change chart type

Changing the property of the chart may be necessary at times when using Excel. This code can be captured in macros and may be helpful to automate the whole process.

'Select your chart first before running this

Sub ChangeChartType()

'This is the clustered column chart, and you can change the type

'Other chart types are listed at: https://docs.microsoft.com/en-us/office/vba/api/Excel.XlChartType

ActiveChart.ChartType = xlColumnClustered

End Sub

48. Use Text to speech

If you have an excel edition with Text to speech installed, you can make excel speak using Macros code.

To specify the cell text to be put to speech, use the code below. For example, our code will speak out cell A1 to change to A2 (1, 2) and so on.

Sub SayThisCell()
Cells(1, 1).Speak
End Sub

Macros can also speak the content of a string by using this code

Sub SayThisString()

Dim SayThis As String

SayThis = "Using excel has given me a nice experience and has made it easy for me in accounting."

Application.Speech.Speak (SayThis)

End Sub

49. Use goal seek

Goal seek is an amazing feature in excel that takes out the guesswork for you and predicts the value of the input needed to attain a specific goal. Let's say you expected outcome in mind but no idea of the starting amount that you should invest. Macros can help you execute such kinds of problems just by running this code.

'Make sure the Worksheet is selected to execute the Goal Seek on

Sub GoalSeekVBA()

Dim TargetGoal As Long

'Get the target value from the user.

TargetGoal = InputBox("Enter the target value", "Enter Goal")

'Make sure to change the cell that you want to be changed with the goal

ActiveSheet.Range("E9").GoalSeek _

Goal:=TargetGoal, _

ChangingCell:=Range("A9")

End Sub

50. Create a backup of a current workbook

If you have a very important workbook that you do not want to lose by any chance and need to frequently back it up, Macros has a Code to sort you out.

Sub CreateBackup()

'Create a backup on the specified folder with the date today included

'Don't forget to change folder."

ThisWorkbook.SaveCopyAs Filename:="C:\ChangeMe\" & Format(Date, "mmddyyyy") & "-" & ThisWorkbook.name

End Sub

I wish this could be done exhaustively, but there is still a lot to learn. Learning is a lifetime work. Using excel is becoming easier and enjoyable and especially with the aid of macros code which automates processes. May this resource is not the end of it. Explore more.