Top 100 Useful Excel Macro [VBA] Codes Examples

Macro codes can save you a ton of time.

You can automate small...

...as well as heavy tasks with VBA codes.

And do you know, with the help of macros...

...you can break all the limitations of Excel which you think Excel has?

And today, I have listed some of the useful codes examples to help you become more productive in your day to day work.

You can use these codes even if you haven't used VBA before that.

These codes will exactly do the same thing which headings are telling you. All you have to do just paste these codes in your VBA editor.

For your convenience, please follow these steps to add these codes to your workbook.

Once you activate developer tab, you can use below steps to paste a VBA code into VB editor.

click here to see the steps

...a list of top 100 macro codes for VBA beginners

I’ve added all the codes into specific categories so you can find your favorite codes quickly.

Just read the title and click on it to get the code.

Important: This is my Ultimate Code Vault which I update on monthly basis with new codes. It would be great if you bookmark this page and keep on visiting to new codes every time.

Basic Codes

These VBA codes will help you to perform some basic tasks in a flash which you frequently do in your spreadsheets.

1. Add Serial Numbers

2. Insert Multiple Columns

3. Insert Multiple Rows

4. Auto Fit Columns

5. Auto Fit Rows

6. Remove Text Wrap

7. Unmerge Cells

8. Open Calculator

9. Add Header/Footer Date

10. Custom Header/Footer

Formatting Codes

These VBA codes will help you to format cells and ranges using some specific criteria and conditions.

11. Highlight Duplicates from Selection

12. Highlight the Active Row and Column

13. Highlight Top 10 Values

14. Highlight Named Ranges

15. Highlight Greater than Values

16. Highlight Lower Than Values

17. Highlight Negative Numbers

18. Highlight Specific Text

19. Highlight Cells with Comments

20. Highlight Alternate Rows in the Selection

21. Highlight Cells with Misspelled Words

22. Count/Highlight Cells With Error in the Entire Worksheet

23. Count/Highlight Cells With A Specific In Entire Worksheet

24. Highlight all the Cells in a Worksheet which are Blank but have an Invisible Space

25. Highlight Max Value In The Range

26. Highlight Min Value In The Range

27. Highlight Unique Values

28. Highlight Difference in Columns

29. Highlight Difference in Rows

Printing Codes

These macro codes will help you to automate some printing tasks which can further save you a ton of time. 

30. Print Comments

31. Print Narrow Margin

32. Print Selection

33. Print Custom Pages

Worksheet Codes

These macro codes will help you to control and manage worksheets in an easy way and save your a lot of time.

34. Hide all but the Active Worksheet

35. Unhide all Hidden Worksheets

36. Delete all but the Active Worksheet

37. Protect all Worksheets Instantly

38. Resize All Charts in a Worksheet

39. Insert Multiple Worksheets

40. Protect Worksheet

41. Un-Protect Worksheet

42. Sort Worksheets

43. Protect all the Cells With Formulas

44. Delete all Blank Worksheets

45. Unhide all Rows and Columns

46. Save Each Worksheet as a Single PDF

47. Disable Page Breaks

Workbook Codes

These codes will help you to perform workbook level tasks in an easy way and with minimum efforts. 

48. Create a Backup of a Current Workbook

49. Close all Workbooks at Once

50. Copy Active Worksheet into a New Workbook

51. Active Workbook in an Email

52. Add Workbook to a Mail Attachment

53. Welcome Message

54. Closing Message

55. Count Open Unsaved Workbooks

Pivot Table Codes

These codes will help you to manage and make some changes in pivot tables in a flash.

56. Hide Pivot Table Subtotals

57. Refresh All Pivot Tables

58. Create a Pivot Table

59. Auto Update Pivot Table Range

60. Disable/Enable Get Pivot Data

Charts Codes

Use these VBA codes to manage charts in Excel and save your lot of time. 

61. Change Chart Type

62. Paste Chart as an Image

63. Add Chart Title

Advanced Codes

Some of the codes which you can use to preform advanced task in your spreadsheets.

64. Save Selected Range as a PDF

65. Create a Table of Content

66. Convert Range into an Image

67. Insert a Linked Picture

68. Use Text to Speech

69. Activate Data Entry Form

70. Use Goal Seek

71. VBA Code to Search on Google

Formula Codes

These codes will help you to calculate or get results which often you do with worksheet functions and formulas.

72. Convert all Formulas into Values

73. Remove Spaces from Selected Cells

74. Remove Characters from a String

75. Add Insert Degree Symbol in Excel

76. Reverse Text

77. Activate R1C1 Reference Style

78. Activate A1 Reference Style

79. Insert Time Range

80. Convert Date into Day

81. Convert Date into Year

82. Remove Time from Date

83. Remove Date from Date and Time

84. Convert to Upper Case

85. Convert to Lower Case

86. Convert to Proper Case

87. Convert to Sentence Case

88. Remove a Character from Selection

89. Word Count from Entire Worksheet

90. Remove the Apostrophe from a Number

91. Remove Decimals from Numbers

92. Multiply all the Values by a Number

93. Add a Number in all the Numbers

94. Calculate the Square Root

95. Calculate the Cube Root

96. Add A-Z Alphabets in a Range

97. Convert Roman Numbers into Arabic Numbers

98. Remove Negative Signs

99. Replace Blank Cells with Zeros

100?

It’s your turn now.

Yes.

I want you to share your favorite macro code with me…

…which you use every day to save your time.

In the end, I just want to say that some of these codes I use every day to increase my productivity…

…and I’m sure it will also help you in your work.

Download Free PDF Copy

Enter your name and email below to get a Free PDF copy directly into your inbox.

Content Protection by DMCA.com
2018-11-21T11:50:15+00:00

83 Comments

  1. Mike Wright 7 Dec, 18 at 10:01 pm - Reply

    Hi I am Looking for a way to convert a Rage Named \0 which is and auto Start Name xls in XP Excel 97-2003 to vba code in Excel 2010 Windows 10 Is there a Way? or do I just need to start over?

  2. Courtney 6 Dec, 18 at 9:55 pm - Reply

    I could not get the code for highlighting the row and column of the cell I’m working on to function:

    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

    Any suggestions?

  3. Martin Schmidt 6 Dec, 18 at 11:40 am - Reply

    Here are some of the codes I use on a daily basis.

    Function ConvertColumnNumberToLetter(colNum)
    ‘Getting the address of the first row and the colNum column number
    colAdr = ActiveWorkbook.ActiveSheet.Cells(1, colNum).Address

    With Application.WorksheetFunction
    colLetter = .Find(“$”, colAdr, 2) ‘Finding the second $-sign in the address
    ConvertColumnNumberToLetter = Mid(colAdr, 2, colLetter – 2) ‘Extracting the middle part of the address, containing only the letter(s) and returning it/them
    End With
    End Function

    Function ConvertColumnLetterToNumber(colLet As String)
    With ActiveWorkbook.ActiveSheet
    colAdr = .range(colLet & 1).Address ‘Getting the address of the first row and the colNum column number
    ConvertColumnLetterToNumber = .range(colAdr).Column ‘Getting the column number of the address
    End With
    End Function

    Function SendSelectionAsEmail(rng As range, subj As String, sendTo As String, Optional ccTo As String, Optional intro As String)

    ‘ Select the range of cells on the active worksheet.
    ActiveSheet.range(rng).Select

    ‘ Show the envelope on the ActiveWorkbook.
    ActiveWorkbook.EnvelopeVisible = True

    ‘ Set the optional introduction field thats adds
    ‘ some header text to the email body. It also sets
    ‘ the To and subject lines. Finally the message
    ‘ is sent.
    With ActiveSheet.MailEnvelope
    .Introduction = intro
    .Item.To = sendTo
    .Item.CC = ccTo
    .Item.Subject = subj
    .Item.Send
    End With
    End Function

    Function ConvertCollectionToArray(col As Collection)
    Dim arr() As Variant
    ReDim arr(1 To col.Count) As Variant
    For i = 1 To col.Count
    arr(i) = col(i)
    Next i
    toArray = arr
    End Function

    Function LastRow(ws As Worksheet, columnNumberToCheck)
    LastRow = ws.Cells(Rows.Count, columnNumberToCheck).End(xlUp).Row
    End Function

    Function LastColumn(ws As Worksheet, rowNumberToCheck)
    LastColumn = ws.Cells(rowNumberToCheck, Columns.Count).End(xlToLeft).Column
    End Function

    Function GetLastRowAdvaned(ws As Worksheet, endColumnNumber) ‘Looping through all columns from 1 to the end column number and finding the max value
    maxVal = 0
    For i = 1 To endColumnNumber
    If LastRow(ws, i) > maxVal Then
    maxVal = LastRow(ws, i)
    End If
    Next i
    GetLastRowAdvaned = maxVal
    End Function

    Function IsRowEmpty(ws As Worksheet, rowNumberToCheck, endColumnNumber) As Boolean
    Dim isEmpty As Boolean
    isEmpty = True

    For i = 1 To endColumnNumber
    If ws.range(Cells(rowNumberToCheck, i).Address) = “” Then
    IsRowEmpty = True
    Else
    IsRowEmpty = False
    GoTo EndFunction:
    End If
    Next i

    EndFunction:
    IsRowEmtpy = isEmpty
    End Function

  4. BRANDON 5 Dec, 18 at 3:40 am - Reply

    This macro will promt you to select a photo, then it will size the height ,width and insert it to a specific range.

    Sub Insert_Setup_Photo()

    ActiveSheet.Protect DrawingObjects:=False

    Dim picToOpen As String
    picToOpen = Application.GetOpenFilename _
    (Title:=”Select Setup Photo To Insert”)

    If picToOpen = “False” Then
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True
    Exit Sub
    End If

    Dim shp As Shape, t As Double, l As Double, w As Double, h As Double, r As Integer
    Dim Cel As Range

    CellHeight = 375 ‘Final Image Height, maintains scale
    CellWidth = 670 ‘Final Image Width, maintains scale

    Set Cel = Range(“B5:M29”) ‘Cells image be centered

    With Cel

    Set shp = ActiveSheet.Shapes.AddPicture(Filename:=picToOpen, _
    LinkToFile:=msoFalse, SaveWithDocument:=msoCTrue, Left:=0, Top:=0, Width:=-1, Height:=-1)
    shp.Locked = False
    shp.Height = CellHeight

    If shp.Width > CellWidth Then
    shp.Width = CellWidth
    End If

    shp.Left = .Left + ((.Width – shp.Width) / 2)
    shp.Top = .Top + ((.Height – shp.Height) / 2)

    End With

    ActiveSheet.Protect DrawingObjects:=False, Contents:=True

    End Sub

  5. Vivekanand Kola 27 Nov, 18 at 2:10 am - Reply

    I Would like move the cursor from active cell to one cell left,right(From selected cell)..same thing to Up and Down by writing macros, Could you please help out resolve the task. I tried to write the below but its not working fine. Please let me know.
    Sub Left(). Ex: Hear Activecell is C2
    activecell.offset(0,-1).select -> hear it moves B2 cell
    end sub
    sub right()
    activecell.offset(0,1) .select -> hear it moves C2 But I want to move to D2. Same thing to Up and Down
    end sub

    Thanks,
    Vivek.

  6. Alpesh 22 Nov, 18 at 12:44 pm - Reply

    hi, I want to create Outlook email macro where emails automatically gets saved in to shared drive. Help needed with coding if anyone knows.

  7. Ritesh Dev 16 Nov, 18 at 9:27 am - Reply

    hi punnet sir

    thank you for providing macro code. These codes provide me the advantage of time in my work. these coding also provide my coding awareness . after having understood from these 100 codes. I have started creating small macro coding.

    thank you very much.

  8. Mahendran R 13 Nov, 18 at 10:14 am - Reply

    Hi Puneet,

    Great job.

    Shall i get code for deleting rows if any of the column contains blank cells

    Thanks

  9. Ed Snyder 14 Oct, 18 at 12:25 pm - Reply

    I need VBR code to work across all worksheets in a workbook a future date will be manually entered in the same cell on every sheet and new sheets are created daily. On the actual day of the date entered an email notification would be sent out I also need the worksheet name in the email so I will know which sheet is due.

  10. Ravi Patel 26 Sep, 18 at 5:17 pm - Reply

    Dear, I am using following code for transferring data from one sheet to another sheet, three variable parameters, i.e. between two dates and center which are selected from Dropdown menu from Main sheet.
    Programme run successfully, but each record written, i should press cancel button, after last record transfer, all data changed. I think, there is formula on CRM(Data) sheet and while transferring data formula also transferred, so data will be changed after running.

    Pl guide me in the matter.

    Sub Module()
    ‘SelectDataBetweenTwoDates()

    ‘declare variables
    Dim fromDate, toDate
    Dim MyResults As Worksheet, myData As Worksheet, MyDates As Worksheet
    Dim mModule As String

    Set MyResults = Worksheets(“MODCRM”)
    Set myData = Worksheets(“CRM”)
    Set MyDates = Worksheets(“Main”)

    ‘clear previous results
    MyResults.Range(“$A$3:$K$450”).ClearContents

    ‘attribute date values to variables
    fromDate = MyDates.Range(“D7”).Value
    toDate = MyDates.Range(“D9”).Value
    mModule = MyDates.Range(“D5”).Value

    ‘convert to text format to allow filtering
    fromDate = Format(fromDate, “dd-mmm-yyyy”)
    toDate = Format(toDate, “dd-mmm-yyyy”)

    With myData
    ‘removes autofilter
    If .FilterMode Then .ShowAllData
    ‘filter the data based on selected date values
    .Range(“$A$2:$K$2”).AutoFilter field:=7, Criteria1:= _
    “>=” & fromDate, Operator:=xlAnd, Criteria2:=”<=" & toDate
    .Range("$A$2:$K$2").AutoFilter field:=4, Criteria1:=mModule

    'copy the filtered data
    .UsedRange.SpecialCells(xlCellTypeVisible).Copy

    'paste copied values to results sheet
    MyResults.Range("A1").PasteSpecial

    End With

    ' remove autofilter in mydata

    'select cell A1 in results sheet
    MyResults.Activate
    MyResults.Range("A1").Select

    End Sub

    Please any one can guide me in the matter.
    while copying (xlCellTypeVisible) data copied with formula instead of values only.

    Pl guide me in the matter.

    Ravi Patel

  11. RAVI PATEL 26 Sep, 18 at 1:12 pm - Reply

    How to reverse vlookup in VBA on bottom 20 records from 100 records ?

  12. RAVI PATEL 26 Sep, 18 at 1:08 pm - Reply

    Hi,

    I am using macro for auto filter on multiple filter with between dates and center, every thing is run successfully, but i have to press cancel button at every record then record display on screen, after last record, all record changed this i due to formula on sheet,

    Any one help me.

  13. jayesh 26 Sep, 18 at 10:57 am - Reply

    Hi,

    I have Stock and requiremets and required Output as given below.
    Stock
    Mat Code Mat Description Batch Avlb STK
    DS1977S40014C01 CO/SAT/400TC/114/IVORY C16184204 100
    DS1977S40014C01 CO/SAT/400TC/114/IVORY C16184206 100
    DS1977S40014C01 CO/SAT/400TC/114/IVORY G15833208 100
    DS1977S40014C01 CO/SAT/400TC/114/IVORY C16150304 750
    DS1977S40014C01 CO/SAT/400TC/114/IVORY C16151502 250
    DS1977S40014C01 CO/SAT/400TC/114/IVORY C16151501 250
    DS1977S40014C01 CO/SAT/400TC/114/IVORY C16150305 600

    Requirements
    SR NO Mat Code Mat Description SO Req Qty
    3161313530 DS1977S40014C01 CO/SAT/400TC/114/IVORY 300.000
    3161313573 DS1977S40014C01 CO/SAT/400TC/114/IVORY 200.000
    3161313574 DS1977S40014C01 CO/SAT/400TC/114/IVORY 200.000
    3161313575 DS1977S40014C01 CO/SAT/400TC/114/IVORY 350.000
    3161313576 DS1977S40014C01 CO/SAT/400TC/114/IVORY 500.000
    3161313538 DS1977S40014C01 CO/SAT/400TC/114/IVORY 200.000
    3161313539 DS1977S40014C01 CO/SAT/400TC/114/IVORY 200.000
    3161313540 DS1977S40014C01 CO/SAT/400TC/114/IVORY 200.000

    Required OutPut:-
    SR No Mat Code Mat Description Req. Qty Batch Available qty Consumed remarks
    3161313530 DS1977S40014C01 CO/SAT/400TC/114/IVORY 100 C16184204 100 100 SO qty Spilt
    3161313530 DS1977S40014C01 CO/SAT/400TC/114/IVORY 100 C16184206 100 100 SO qty Spilt
    3161313530 DS1977S40014C01 CO/SAT/400TC/114/IVORY 100 G15833208 100 100 SO qty Spilt
    3161313573 DS1977S40014C01 CO/SAT/400TC/114/IVORY 200 C16150304 750 200 Batch qty Spilt
    3161313574 DS1977S40014C01 CO/SAT/400TC/114/IVORY 200 C16150304 550 200 Batch qty Spilt
    3161313575 DS1977S40014C01 CO/SAT/400TC/114/IVORY 350 C16150304 350 350 Batch qty Spilt
    3161313576 DS1977S40014C01 CO/SAT/400TC/114/IVORY 250 C16151502 250 250 SO qty Spilt
    3161313576 DS1977S40014C01 CO/SAT/400TC/114/IVORY 250 C16151501 250 250 SO qty Spilt
    3161313538 DS1977S40014C01 CO/SAT/400TC/114/IVORY 200 C16150305 600 200 Batch qty Spilt
    3161313539 DS1977S40014C01 CO/SAT/400TC/114/IVORY 200 C16150305 400 200 Batch qty Spilt
    3161313540 DS1977S40014C01 CO/SAT/400TC/114/IVORY 200 C16150305 200 200 Batch qty Spilt

    Pls help on this..

  14. A K Ojha 20 Sep, 18 at 6:13 pm - Reply

    Sub SaveAs()

    ‘ SaveAs Macro

    ‘ Keyboard Shortcut: Ctrl+Shift+A

    ChDir “D:”
    ActiveWorkbook.SaveAs Filename:= _
    “D:gst Billing System2018.xlsm”, FileFormat:= _
    xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    ChDir “E:JSM”
    ActiveWorkbook.SaveAs Filename:=”E:JSMBilling System2018.xlsm”, FileFormat:= _
    xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    End Sub

  15. Ram 19 Sep, 18 at 7:35 pm - Reply

    Nice work

  16. Jeffery 18 Sep, 18 at 11:49 am - Reply

    Very well articulated, useful tool and helpful. We’ll done, excellent champ

  17. T2 10 Sep, 18 at 4:39 pm - Reply

    Thanks Puneet, nice work

  18. Jaimin Mistry 6 Sep, 18 at 2:26 am - Reply

    How to run macro for collecting data from read only file?

  19. PC 3 Sep, 18 at 2:47 pm - Reply

    Thanks Puneet, this is so helpful

    Need your help
    Can you help create a macro for this instance in excel:

    N/A
    Abc
    123
    (Blank Cell)
    XYZ
    N/A
    123-222
    N/A
    (Blank Cell)

    Answer: Abc, 123, XYZ and 123-222

    Thanks in advance

  20. Prasad M 24 Aug, 18 at 12:28 pm - Reply

    Hey Hi,
    Thanks for the codes.
    I need your help to count the excel cell colors which used by conditional formatting. I had tried many ways, but, no luck.

    Kindly let me know, if you can help.

  21. Raja 18 Aug, 18 at 8:23 am - Reply

    Excellent would need your support

  22. manish Chaurasiya 6 Aug, 18 at 8:12 pm - Reply

    Thank you for sharing code in easier way, this is very helpful. I am just beginner in macro. I have written a code to connect sql database but i m getting difficulty to connect more than one database from different server.

  23. Sally 30 Jul, 18 at 12:48 am - Reply

    Great list! Thank you!

    • Puneet 3 Aug, 18 at 9:12 am - Reply

      Thanks for your words.

  24. loran shahin 26 Jul, 18 at 8:21 am - Reply

    thank Puneet Gogia

    • Puneet 28 Jul, 18 at 6:05 pm - Reply

      You are welcome. ?

  25. Guru 23 Jul, 18 at 4:50 am - Reply

    Thanks Puneet, this is so helpful!

    • Puneet 23 Jul, 18 at 7:04 am - Reply

      You are welcome. 🙂

  26. Gideon 23 Jan, 18 at 10:31 am - Reply

    Please I want the VBA code to merge multiple excel sheet in one. Can you send me the code please.

  27. Steven Brown 19 Jan, 18 at 3:15 pm - Reply

    I’m getting a type mismatch when I run the ‘HighlightAlternateRows’ procedure. Debug shows this line as the culprit:
    ‘rng.Value = rng ^ (1 / 3)’
    Any help would be appreciated. Thanks

    • Steven Brown 22 Jan, 18 at 3:47 pm - Reply

      I fixed it and it works!

      Sub Highlight AlternateRows ()
      Dim rng As Range
      __For Each rng In Selection.Rows
      ____If rng.Row Mod 2 = 1 Then
      ____rng.Style = “20% – Accent1”
      ____Else
      ____End If
      __Next rng
      End Sub

      (underscores added to show proper indenting)

  28. Carlos Mario Castaño 3 Jan, 18 at 4:16 am - Reply

    1. In Insert Multiple Rows please change the word “columns” by “rows” in
    i = InputBox(“Enter number of columns to insert”, “Insert Columns”)

    2. Closing Message
    “You can use close_open to perform a task on opening a file. All you have to do just name your macro
    “close_open”.”
    Sub auto_close()
    The name of the macro is not “close_open”
    3. Count/Highlight Cells With Error In Entire Worksheet
    These statements must be in different lines:
    i = i + 1 rng.Style = “bad”
    4. In Count/Highlight Cells With A Specific In Entire Worksheet please add the word “Value” after “Specific”

  29. Carlos Mario Castaño 24 Dec, 17 at 10:42 pm - Reply

    Although it worked for me, in the first Basic Macro, I have two observations:

    1) I think that the For loop must be something like:
    Dim j as integer
    For j = 1 to i
    ActiveCell.Value = j
    ActiveCell.Offset(1, 0).Activate
    Next j
    that is, replace “i” by j in the index variable for the loop
    2) As I said, your original code works but I think that the index variable must be different to the inputbox variable.
    Sincerely yours,
    Carlos

  30. Addison O'Conner 8 Dec, 17 at 5:55 pm - Reply

    Hello! I would like a very specific code that I haven’t been able to find anywhere on the internet…
    I’m wanting the macro to identify blank cells in Row 1 only, delete the blank cells (in Row 1) & shift those columns’ cells up. Any advice??

    • Chris 10 Aug, 18 at 2:38 pm - Reply

      Try this:
      ‘ DeleteBlankCellsinRow1 Macro
      Sub DeleteBlankCellsinRow1()
      Rows(“1:1”).Select
      Selection.SpecialCells(xlCellTypeBlanks).Select
      Selection.Delete Shift:=xlUp
      End Sub

  31. Ron007 7 Dec, 17 at 4:33 pm - Reply

    Great list, but it would be nice if you made it easier for us to identify changes in it since you say you make monthly changes.

    Rather than a PDF, how about putting this tips in a downloadable spreadsheet or Word document that the tips can be sorted by insertion date as well as category. Actually. If you make the download cumulative that would be even better. So each month we could replace the old file with the new one, containing the all of the tips you’ve published here over time. Even if you start now building the cumulative file, that would be better.

    • Puneet Gogia 8 Dec, 17 at 5:35 am - Reply

      Thanks for the great advice.

  32. S raw 29 Oct, 17 at 5:44 pm - Reply

    Hello Sir,

    I want to learn how to create macro. I have the basic knowledge of VB. Please advise from where should I start.

  33. Pravin Bhaiswar 12 Oct, 17 at 11:28 am - Reply

    Sir, It’s extremely nice efforts. Would you save your precious time to modify one code of you you did? I need it should ask us location to save ask us to rename the file name before save………in the code for “Save Selected Range as a PDF” please modify this…..it will be beneficial for all…….please send me to bhaiswarpravin@gmail.com

    • Puneet Gogia 12 Oct, 17 at 11:38 am - Reply

      Thanks you Pravin, for the correction. Will correct it soon,

      • Pravin Bhaiswar 12 Oct, 17 at 1:27 pm - Reply

        Sir, I used this code at my office…it was nice working but on my home pc..it says “Run time error 5: Invalid procedure call or argument…….So please have solution sir.

        • Puneet Gogia 13 Oct, 17 at 4:43 am - Reply

          Which version of Microsoft Office you are using?

          • Pravin Bhaiswar 13 Oct, 17 at 7:55 am

            Office 2007

  34. Niharika Mehra 26 Sep, 17 at 3:58 pm - Reply

    Hi.. Can someone help me with the vba code to apply filter in pivot table.

  35. Khaja Raziuddin 24 Sep, 17 at 9:50 am - Reply

    Hello, really nice to see all these. is there any macro to copy data from different file to master file. if yes then can anyone please share

    • Puneet Gogia 24 Sep, 17 at 3:10 pm - Reply

      You can use power query from that.

  36. Colleen Armstrong 31 Aug, 17 at 3:00 am - Reply

    Thank you for these!!! I am going to start putting many of the to use tomorrow!!

    • Puneet Gogia 31 Aug, 17 at 6:35 pm - Reply

      That’s great.

  37. Himanshu Tiwari 30 Aug, 17 at 12:05 pm - Reply

    Nice Work.

    I was also trying to get in touch with you to understand if there is a way we can filter a table based on unique values

    Let’s say we have the following table on the meetings done by a person, and the start time of each meeting

    Date Start Time
    ———- ————–
    Aug 24 9:00 AM
    Aug 24 10:00 AM
    Aug 24 1:45 PM
    Aug 24 4:45 PM
    Aug 25 8:00 AM
    Aug 25 2:00 PM
    Aug 25 5:00 PM
    Aug 26 12:30 PM
    Aug 26 2:00 PM
    Aug 26 4:29 PM
    Aug 26 8:28 PM

    Now I need to calculate the average start time for the period (say week / month) from such a table.
    How to do so?

    The best way I have right now is copy both the columns, and check for duplicates in the Date column, followed by which I get to calculate the average start time. Something like this.
    Date Start Time
    ——— ——————-
    Aug 24 9:00 AM
    Aug 25 8:00 AM
    Aug 26 12:30 PM

    Can we build a formula to decrease the above task.

  38. Dilshad Ahmad 30 Aug, 17 at 10:23 am - Reply

    Awesome! Puneet !

    • Puneet Gogia 31 Aug, 17 at 6:35 pm - Reply

      Thanks for your words.

  39. Dhananjay Jadhav 30 Aug, 17 at 7:24 am - Reply

    Great Puneet! Many of these are new & innovative for me. I am sure it will help me save my hours of daily work. Thanks much

    • Puneet Gogia 31 Aug, 17 at 6:35 pm - Reply

      I’m so glad you liked it.

  40. Hema Deepak Raturi 13 Aug, 17 at 3:38 am - Reply

    How to transfer a cell value from main workbook to several workbooks via VBA program, without opening the other several workbooks.

  41. Jeff Faul 27 Jul, 17 at 2:50 pm - Reply

    Hey, great macros. Question, I’m using macro 31 “add rows textbox” I’m adding rows at line 35 and then I need to copy the formula from h34 down to all the new rows. Could you help with this addition?

    • Puneet Gogia 27 Jul, 17 at 5:33 pm - Reply

      I assume you want to insert a new row and copy formula as well?

      • Jeff Faul 27 Jul, 17 at 10:33 pm - Reply

        Yes, whatever number of rows are added I need the formula copied to every new row

      • Jeff Faul 30 Jul, 17 at 10:38 pm - Reply

        Here is what I currently have:

        Sub InsertMultipleRows()
        Dim i As Integer
        Dim j As Integer
        Rows(“35:35”).Select
        On Error GoTo Last
        i = InputBox(“Enter number of items to add”, “Insert Items”)
        For j = 1 To i
        Selection.Insert Shift:=xlToDown, CopyOrigin:=xlFormatFromRightorAbove
        Next j
        Last:
        Range(“H34”).Select
        Selection.AutoFill Destination:=Range(“H34:H35”), Type:=xlFillDefault
        ‘Range(“H34″+i).Select (This is the part I need help with)
        Range(“C35”).Select
        Exit Sub
        End Sub

        • Jeff Faul 5 Aug, 17 at 2:33 am - Reply

          Nevermind, I figured it out

          • Puneet Gogia 5 Aug, 17 at 7:45 am

            I’m sorry I just missed your update. Please share with me here so that other can make use of it.

  42. Kushal R Jaju 29 May, 17 at 6:56 am - Reply

    Hi Puneet.. It’s a great website and I’m learning something every day. Thanks for that..

    Save as PDF Command not working, can you please help. ?

    • Puneet Gogia 31 May, 17 at 10:21 am - Reply

      Please share the error you got.

  43. Venkateshwara Iyer 7 May, 17 at 3:28 am - Reply

    i like to have the code to convert text to uppercase for entire sheet

    • Puneet Gogia 30 May, 17 at 10:57 am - Reply

      updated

  44. Venkateshwara Iyer 7 May, 17 at 3:28 am - Reply

    SUPERB

    • Puneet Gogia 30 May, 17 at 2:14 pm - Reply

      thank you

  45. Vipul 1 Apr, 17 at 4:13 am - Reply

    Great
    Really helpful
    Would like to see more VBA codes.

    Thanks

    • Puneet Gogia 30 May, 17 at 10:58 am - Reply

      updated new codes

  46. vishesh 1 Mar, 17 at 5:14 am - Reply

    Punit i am very novice to VB, so can you give some tips how to grow up in VB coding, your kind guidance is required, my email id is gshuvishesh@gmail.com

    • Puneet Gogia 15 Mar, 17 at 8:21 am - Reply

      Added.

  47. vishesh 1 Mar, 17 at 5:11 am - Reply

    Thanks Punit for sharing Wonderful Excel tricks….Helped me to automate my few daily routine task in one go….

    • Puneet Gogia 15 Mar, 17 at 8:21 am - Reply

      I’m so glad you liked it.

  48. gabriel gajardo 17 Feb, 17 at 4:03 pm - Reply

    hi!
    i work a lot with vba, a have some codes that can be useful, lake a parametric sendMail or send a worksheet or range as body mail. if you wan to add the just get in touch.

    by the way, thanks for share some codes.

    • Puneet Gogia 15 Mar, 17 at 8:22 am - Reply

      Thank you, you can share with me.

    • zenix 7 Sep, 18 at 2:07 am - Reply

      Thank you in advance for your generosity.

    • Abhiram G 6 Oct, 18 at 1:22 pm - Reply

      hi Gabriel could you pls share this code to my mail ID also. abhiram.dilip@gmail.com . Thanks in advance

  49. ratanak 23 Mar, 16 at 2:02 am - Reply

    Really nice,i’d love it.thanks puneet

    • Puneet Gogia 23 Mar, 16 at 6:09 am - Reply

      Thanks Ratanak, For Your Words

  50. Inet Kemp 21 Feb, 16 at 7:01 pm - Reply

    nice…highlight active row and column

    • Puneet Gogia 3 Mar, 16 at 5:35 pm - Reply

      Thanks Inet

Leave A Comment