Excel VBA

76 thoughts on “Excel VBA

  1. Ravikumar Singh

    Dear Takyar Sir,

    I was going through your postings on youtube and I am very happy to understand from your easy and effective way of explanation. I am student at New York University and working on a project. I need email notification to the user( email to their outlook) from the highlighted or conditional formated cell whenever the condition is true. I will need to send the email on regular basis whenever I open my excel file which is link to the reports they have submitted. Can I defined a time to send the email notification. Your help will be highly appreciated. Let me know if I can be of any help for you anytime. Thank you so much in advance. Please write me back on my email id ors216@nyu.edu


  2. kailashpati Malakar

    How can I can create salary data like Basic Pay, D.A, H.R.A, O.A Gross salary, deductions,Net amount in MS Excel VBA

  3. kailashpati Malakar

    Dear Takyar Sir,
    I am aged about 62 years & trying myself to understand Excel VBA , your video tutorial & explanation in all respect is highly appreciable as you show & describe everything pointed with the voices description, however I am trying to create the following in MS excel ,How can I can create salary data like Basic Pay, D.A, H.R.A, O.A Gross salary, deductions,Net amount in MS Excel VBA
    With regards

      1. rashi

        Hi Sir,

        I want to learn excel vba from basics.
        I have no knowledge on it.
        please share me the videos from basics till the end..

        Rashi Soni

          1. Hassan

            Dear Mr Takyar,

            I want to copy column A from a several workbooks into a spread sheet of a summary work book IF the copy workbooks name matches cells of row 1(or 2) in the paste workbook (summary workbook).

            Below is a code that might be adjusted;

            [B]Sub copydata()
            Dim FolderPath As String, Filepath As String, Filename As String
            FolderPath = “C:\mandar-test\”
            Filepath = FolderPath & “*.xlsx”
            Filename = Dir(Filepath)
            Dim erow As Long, lastrow As Long, lastcolumn As Long

            Do While Filename “”
            Dim wb As Workbook
            Set wb = Workbooks.Open(FolderPath & Filename)
            For counter = 1 To 3
            lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
            lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
            Range(Cells(2, 1), Cells(lastrow, lastcolumn)).Copy

            ‘ Sheets(“Sheet1”).Select
            erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            Cells(erow, 1).Select
            wb.Close savechanges:=False

            Filename = Dir


            erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            Cells(erow, 1).Select

            End Sub

            Another issue am facing is that I am trying to consolidate time series data, but every one of these individual copy workbooks has different starting dates, hence there is some missing data. how can I make another condition for the pasting of data in summary workbook to paste in column B (if its header tallies with the source workbook) starting from the row that corresponds with column A dates.
            I am attaching files for convenience.

            Kind Regards,

      2. Jafir Khan

        Dear sir I am facing too many lines continuation problem in VBA ! Please help me to solve the problem! Or please give the relevant video Link on YouTube!

  4. Javed Ashraf Khan

    Sir! I am facing problem in applying Nested IF function,Syntax is exactly correct, but the result isunexpected. in the selected column the result returns in exactly as required but in the same column there are cells in between which do not return result but comes blank. I ‘ve tried my best to trace the reason but of no use.
    Kindly guide to the expected reasons for such adamant and resistent errors. Waiting for your reply anxiously.
    Regards Javed
    From Umerzai Pakistan

  5. Gangadhara

    Hi Dinesh,

    I am Gangadhara working as a application support engineer, I am started learning Excel VBA your youtube video’s are very much use full to me. I have a “requirement where I need to restrict only a group of predefined user can access the workbook if the user is not a person from predefined list then Excel should close with a warning message”.

    Cloud you please assist on this ?

  6. Piyuesh

    I want to make an automated excel sheet for my work.. Can you help me in that ? 9620894009, Bangalore

  7. chandu

    Hi sir,
    I have two sheets(Manual and automation)….i have to copy and paste entire data from manual and automation sheets and paste it to third sheet(consolidated) automatically using VBA.

    Note:first we have to place Manual data and we have to give a five rows gap and then we have to place automation data.

    1. chandu

      Hi sir,
      I have two sheets(Manual and automation)….i have to copy and paste entire data from manual and automation sheets and paste it to third sheet(consolidated) automatically using VBA.

      Note:first we have to place Manual data and we have to give a five rows gap and then we have to place automation data.

  8. Terry Head

    I would like to restrict the data entered ian inputbox to a six digit Julian date format, example 14 (year) 09 (month) 25 (day): 140925. How can I do this.

    1. Yash Mayekar Yashm22

      here I Have created formula, date in column C is like “20161118”

  9. Achmad Chabib Nursalim

    sir, I want to ask some help to you..
    I have some sheets in excel. I input data to sheet using vb. I have a userform and I put search button in this userform. Then I can load some data if I input some keyword to it. And my problem is how to update data if the are some correction it. I have learn from your video, but I have’t got any solving. For detail of my project, I can email it to you. thanks.
    *please respone

  10. Piyush Rach

    Sir, I have 5 different Sales Associate data with me in 5 different sheets, i want to protect each sheet in such a way that each Sales Associate can view only his data and cannot view all the other sheet. Is this possible in one excel file pls let me know. Thank you

    1. Starr

      Bonjour Ol;#&erCiv8217iest comme cela qu’on va vous aimer, il y tellement de faux cul qui passent chez Doze, on va vite vous remarquer, mais surtout les idées que vous défendez.

      1. Lenna

        Mas o que é que esperavas das bestas que estão a tratar disto?Uns inuteis, incapazes de fazer seja o que for que não seja meter a pata na poça, desta vez fizeram-no com a cagança do cosmtue.Uma merda em grande estilo.Só fazem merda.

      2. http://www.freshpromocodes.info/

        "Is the frequency of sex the bellwether of a good healthy satisfying relationship?"I think banding the frequency is an appropriate proxy. More than once a week? Can't hack that without really being into it. Less than once a week? Believable. Less than once a month? Probably something wrong going on.

  11. Sunil

    Hi Dinesh Sir,

    I have been learning Excel/VBA from your videos. Thanks for your help.

    I have an issue, which i hope you can help me to resolve.

    I am trying to connect to the Sybase server through VBA and run the sql file that contains a set of sql lines (sometimes more than 60 lines).

    I have written the code as below. However, it is giving me error that ” There is an incorrect statement around ‘\'”

    Please help.

    Sub sqltest()

    Dim conn As ADODB.Connection
    Dim cmd As ADODB.Command

    Set conn = New ADODB.Connection
    conn.Open “DRIVER={Sybase ASE ODBC Driver};UID=” & ThisWorkbook.Sheets(“Sheet1”).Cells(2, 13).Value & “;pwd=” & ThisWorkbook.Sheets(“Sheet1”).Cells(2, 14).Value & “;NA= ; CommandTimeout = 50000 ;ConnectionTimeout = 50000; ConnectionIdleTimeout = 50000;Connection LifeTime = 50000;LoginTimeout = 50000;AlternateServers = ;”

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = conn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = “\\\pag\OffShore PAG\Parallel Run\Reports\Sunil\Mymacros\sample macros\SQL NC DB.sql”


    Set conn = Nothing
    Set cmd = Nothing

    End Sub

    Above is the code i am trying to run. However, i do not want to run the macro with the sql lines in the above code as there are many such queries that needs to be run. Hence want a macro that will call the sql file and execute, then display the results in the Excel sheet.


    1. JOeL

      “\\\pag\OffShore PAG\Parallel Run\Reports\Sunil\Mymacros\sample
      should be?
      “\\pag\OffShore PAG\Parallel Run\Reports\Sunil\Mymacros\sample

  12. Joel

    Hi I will be getting a large sheet with data formatted by the columns.How do I use visual basics to import each column one by one into a template then save as each one

  13. Pallavi

    hi, can you tell me how do i automatically run a certain macro when a specific excel file is getting added to a certain folder ?

  14. Henry Leung

    Dear Mr Takyar
    I love the video which you taught us how to use a single macro to automatically transfer data from multiple workbooks into one specific workbook. The example you used is how to copy one row of data (I believe the row has four cells) from each supplier excel file (I believe there are 3 files) into a zmaster.xlsm file. MY problem is similar but instead of copying rows, I need to copy one column from each excel file into the master file. Everything else should be similar except the following line definitely needs to be modified to make it work with columns instead of rows:
    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Can you teach me how to do it? Your help is very much appreciated. Thank you Sir!

  15. Henry Leung

    Dear Mr Takyar

    I tried to copy specific range of cells from multiple workbooks to target workbook with following macro but it didn’t work.

    Sub CopyColumnToWorkbook()
    Dim sourceRange As Range, targetRange As Range
    Dim MyFile As String
    Dim Filepath As String
    Filepath = “C:\Work\Excel_Tutorial2\”
    MyFile = Dir(Filepath)
    Workbooks.Open (Filepath & MyFile)

    Set sourceRange = Workbooks(“barrie.csv”).Worksheets(1).Range(“M3:M13”)
    Set targetRange = Workbooks(“medscheckmacro.xlsm”).Worksheets(1).Range(“B3:B13”)
    sourceRange.Copy Destination:=targetRange

    Set sourceRange = Workbooks(“brantford.csv”).Worksheets(1).Range(“M3:M13”)
    Set targetRange = Workbooks(“medscheckmacro.xlsm”).Worksheets(1).Range(“C3:C13”)
    sourceRange.Copy Destination:=targetRange

    Set sourceRange = Workbooks(“kingston.csv”).Worksheets(1).Range(“M3:M13”)
    Set targetRange = Workbooks(“medscheckmacro.xlsm”).Worksheets(1).Range(“D3:D13”)
    sourceRange.Copy Destination:=targetRange

    End Sub
    The cells from first workbook “barrie.csv” was copied to “medcheckmacro.xlsm” OK, but macro cannot move on to copy workbook “Brantford.csv”. Please point out what did I do wrong.

    Also how can I copy from “all” workbooks inside a folder, using a “loop” instead of having to specify each file inside the folder?

    Thank you so much for your help again.

  16. Marius

    Dear Mr Takyar
    I am trying to costruct a macro that can compare a range of cells with another range, eg
    Sheet 1(A1:F1) contain 6 numerical values (10; 19; 27; 39; 46; 47)
    Sheet 1(A2:F2) contain 6 numerical values (5; 13; 22; 29; 36; 38)

    Sheet 2(A1:F49) contain 49 numerical values (1:49) in no particular order.
    I want the macro to find the instance where the values in Sheet 1(A1:F1) occur in Sheet 2(A1:F49). When the match is found, the code must compare the values of the next row, Sheet 1(A2:F2) with Sheet 2(A1:F49). Ideally I would like to know how to compare the values of one range of cells to the values of another range instead of cell by cell. (this results in the error “too many line continuations).

    Thanks in advance

  17. Patrick Hodgson

    Hello Mt Takyar

    I have been watching your lessons on VBA and have found it fascinating how so many things I do can be improved. I think you teaching style is very good and helpful to novice users like me. I have used your macro for automatic login for my Hotmail account and find it makes things a lot easier. Thank you for sharing this. I am trying to do the same to login to a customer portal (A task I do every day – time consuming!!) but I am struggling to find the input source code items. I have tried various things but it still does not work!!

    please can you help?

    thanks in advance


  18. mahmoud yassein

    i tried to copy coumns of different files into one master file in rows using the special paste command but it shows error “runtime 404” , then i tried to make paste in a row range but also it shows erorr that the paste shape and size is not matched

    plz help me to solve it as soon as possible

  19. Raghavendra.Rao

    Hi Sir, I have a question here, how to open the excel workbook from the folder name called Dump File, excel workbook may be saved in xlsx format or xlsm format. Any of these format match it should open. I tried to use Pattern matching, but could not able to get the answer.
    Pattern match will work for this? Or is there any other alternative to solve this?.Please help me for this.


  20. iam

    Dear dinesh Sir,

    Thank you very much for your service. I very proud of you. Sir can upload some example : excel templates . it will be very very useful.

    Thanks in advance.

  21. Ajendra Kr. Singh

    Sir, I have learn many things from ur videos. thnx for such type of help and I also need vba code for copy data of multiple filters in excel sheet and paste it to different different sheet.

  22. Sandeep Sarode

    Dear Dinesh Sir,

    I am very new to Excel VBA… I created one Form contain two Combobox based on data in Worksheet (“Leave_Record”). Worksheet contain columns like Staff_code, Name, Leave_taken (dates), settled_date (Value “yes or blank cell “). On Userform Combobox1 select the Staff_code and on selection of staff_code combobox2 populate the all leave dates against that staff_code where settle_date cell is blank. How I can use SQL select query for the same or is their any other option available to perform the task.

    Thank in advance for your future help.

    Sandeep Sarode

  23. Thapan Mohan

    Dear Mr.Dinesh ,

    Good Day , Hope all is well !!!

    Your website and videos in excel are really helpful to professionals at any level and I have been a keen student of yours for the last 1 year .thank you for all of your videos .Kindly spare some time resolve the issue posted below , where we are really struggling .

    File 1 : To record the project status and want to add an additional sheet to list the PO’s raised against under this project .

    File 2 : Master file , where we are registering all the PO’s issued , which consist of columns project number , PO number , supplier name , Item details . item qty , etc …. there will be several PO’s issued under one project . ( Note : this file is password protected )

    Want to add a command button in new sheet of file 1

    1) open master file , password
    2) v look up for project number in project number column ( several Po’s)
    3 ) copy the details such as PO number ,supplier name , item , qty
    4) list them in ascending order of PO numbers .

    hope above requirement is clear , kindly help us with vba code for the same .


  24. Altaf Mohamed Akram

    Dear Sir,
    I have a excel file containing serial number in column A and card number in column B and pin number in column C. Upon customer request, we need to mark the master file the serial is used and copy that marked coulumns into new worksheet and send this file to my customer by outlook.
    Eg. I have 1000 rows in coulun A,B,C. My customer is asking for 100 cards, so I copy 100 columns in new worksheet and provide to the customer. In the master file I colour 100 rows which denoted that they are used.

  25. J. Kelley

    Dear Sir:
    I have utilized some of the coding you have mentioned in a video, Transferring Data from one excel workbook to another automatically, to be able to input new data into an existing workbook that we use for storm shelters. I have to code working fine as far as input method, with a mydata.save and a mydata.close at the end to close the data gathering workbook. My question is when it returns to the input workbook is there a way to clear the fields of the previously entered data? I can get this to work if I use the same workbook with two sheets, but am having trouble with two separate workbooks.

    Thank You for your videos as they are very helpful.

    J. Kelley

  26. Venkatasubramanian

    Dear Sir,
    Your videos are very helpful to understand excel.

    I have a small request. I am looking for a Macro, that could send a specified excel row as email to a particular user( whose email address would be in the same excel sheet itself).

    In other words, i am looking at a way to send a Progress report type data to a particular person, on to his email address,through Outlook.

    Since my sheet would contain a lot of use entries, i was exploring to see if Excel macro could help me here.

    Kindly help share a video that solve this problem for me.


  27. L. Taylor

    Dear Mr. Takyar,

    First I would like to thank you for sharing so many great videos on VB/Excel with others. I am hoping you can assist with a request my employer has tasked me to complete as soon as possible. To be honest, I am a bit overwhelmed in that I have no experience working with VB/excel and the project is rather complex. I have tried to explain the project in detail below:

    I need to automate updates to multiple workbooks into one master spreadsheet (all workbooks housed in sharepoint) in Excel 2010. My ultimate goal is to be able to copy changes from multiple workbooks throughout the day to the corresponding row in the master spreadsheet.
    To provide more information: There are approximately 20 workbooks with pivot tables which are being updated throughout the day by multiple individuals. All of the updates in each spreadsheet must be added to a master spreadsheet in a different workbook. *Note – the multiple workbook data is simply a copied subset from the master workbook, thus, the same pivot tables are in all of the workbooks. I will not request that others make changes to the columns which have the pivot tables (columns C, E, F and G). However, users seem to constantly add new applications (again columns “C”) to the list so it is possible that there will be a need for more rows of data in the future…beyond 641. With that in mind, it would be nice for changes in column “C” to display in a different color

    Column 3 (C3:C641) – to copy all updates in column C3:C641 from each of the 20 spreadsheets, and populate master spreadsheet column 3 / Pivot Table: Sort 4: Application/Product Name
    Column 5 (E3:E641) – to copy all updates in column E3:E641 from each of the 20 spreadsheets, and populate master spreadsheet column 5 / Pivot Table: Sort 1: Managing Director List of Names – Note* New names may be added by users
    Column 6 (F3:F641) – to copy all updates in column F3:F641 from each of the 20 spreadsheets, and populate master spreadsheet column 6 / Sort 2: Division Director
    Column 7 (G3:G641) – to copy all updates in column G3:G641 from each of the 20 spreadsheets, and populate master spreadsheet column 7 / Sort 3: Area Manager

    The columns that will be updated on a regular basis in the 20 workbooks all include drop down lists:

    Column 9 (I3:I641) – to copy all updates in column I3:I641 from each of the 20 spreadsheets, and populate master spreadsheet column 9 / Drop down options: Complete – No Remediation, Complete – Remediation Needed, In Progress, Not Started
    Column 10 (J3:J641) – to copy all updates in column J3:J641 from each of the 20 spreadsheets, and populate master spreadsheet column 10 / Drop Down Options: List of Names – Note* New names may be added by users
    Column 11 (K3:K641) – to copy all updates in column K3:K641 from each of the 20 spreadsheets, and populate master spreadsheet column 11 / Drop Down Options – IE11, IE11 Compatibility, IE11 Enterprise
    Column 12 (L3:L641) – to copy all updates in column L3:L641 from each of the 20 spreadsheets, and populate master spreadsheet column 12 Drop Down Options: N/A, Pass
    Column 13 (M3:M641) – to copy all updates in column M3:M641 from each of the 20 spreadsheets, and populate master spreadsheet column 13 / Drop Down Options: 0,75, N/A *Note new options may be added by users
    Column 14 (N3:N641) – to copy all updates in column N3:N641 from each of the 20 spreadsheets, and populate master spreadsheet workbook column 14 / Drop Down Options: N/A – *Note new options may be added by users
    Column 15 (O3:O641) – to copy all updates in column O3:O641 from each of the 20 spreadsheets, and populate master spreadsheet workbook column 15 Free Form Column for comments
    Column 16 (P3:P641) – to copy all updates in column P3:P641 from each of the 20 spreadsheets, and populate master spreadsheet column 16 / Drop Down Options: 2014, 2015, *Note dates may be added in MM/DD/YYYY form
    Column 17 (Q3:Q641) – to copy all updates in column Q3:Q641 from each of the 20 spreadsheets, and populate master spreadsheet column 17/ Drop Down Options: 2014, 2015, *Note dates may be added in MM/DD/YYYY form
    Column 18 (R3:R641) – to copy all updates in column R3:R641 from each of the 20 spreadsheets, and populate master spreadsheet column 18 / Drop Down Options: 0, N, NA, Y, Blank
    Column 19 (S3:S641) – to copy all updates in column S3:S641 from each of the 20 spreadsheets, and populate master spreadsheet column 19/ Drop Down Options: N, Y, Blank
    Column 20 (T3:T641) – to copy all updates in column T3:T641 from each of the 20 spreadsheets, and populate master spreadsheet column 20 /Drop Down Options: N, Y, Blank
    Column 21 (U3:U641) – to copy all updates in column U3:U641 from each of the 20 spreadsheets, and populate master spreadsheet column 21 / Free Form Column for comments

    After reviewing some posts on another site, I came across a case very similar to my situation. Below is a copy of the code provided in response to the user’s inquiry. If, I modify the code below to describe my columns in the spreadsheets, would that be enough to successfully complete my updates?

    Initial response provided to someone with a similar request:

    1. Sub runMerge()Dim fs As FileSystemObject
    2. Dim targetPath As String
    3. Dim haveTarget As Boolean
    4. Dim thefile As File
    5. Dim currRow As Long
    6. Dim sourceFile As String
    11. Dim starttime As Date
    12. Dim endtime As Date
    17. starttime = Now
    22. haveTarget = False
    23. Set fs = CreateObject(“Scripting.FileSystemObject”)
    24. While Not haveTarget
    25. targetPath = InputBox(“Result Folder”, “Target Folder”, “S:\Wealth Management\UWM Committee\Spreadsheet Import\Form 001 – New Account Approval”)
    26. haveTarget = fs.FolderExists(targetPath)
    27. Wend
    28. Set theFolder = fs.GetFolder(targetPath)
    29. currRow = 6
    30. For Each thefile In theFolder.Files
    31. If (InStr(1, thefile.Name, “xls”, vbTextCompare) > 0 And Left(thefile.Name, 1) “~”) Then
    32. Workbooks.Open thefile.Path
    33. sourceFile = ActiveWorkbook.Name
    34. ThisWorkbook.ActiveSheet.Cells(currRow, 1) = Workbooks(sourceFile).Sheets(“001-New Acct Approval”).Cells(9, 8)
    35. ThisWorkbook.ActiveSheet.Cells(currRow, 2) = Workbooks(sourceFile).Sheets(“001-New Acct Approval”).Cells(51, 13)
    36. ThisWorkbook.ActiveSheet.Cells(currRow, 3) = Workbooks(sourceFile).Sheets(“001-New Acct Approval”).Cells(32, 4)
    37. ThisWorkbook.ActiveSheet.Cells(currRow, 4) = Workbooks(sourceFile).Sheets(“001-New Acct Approval”).Cells(14, 7)
    38. ThisWorkbook.ActiveSheet.Cells(currRow, 5) = Workbooks(sourceFile).Sheets(“001-New Acct Approval”).Cells(6, 1)
    39. ThisWorkbook.ActiveSheet.Cells(currRow, 6) = Workbooks(sourceFile).Sheets(“001-New Acct Approval”).Cells(10, 3)
    40. ThisWorkbook.ActiveSheet.Cells(currRow, 7) = Workbooks(sourceFile).Sheets(“001-New Acct Approval”).Cells(10, 8)
    41. ThisWorkbook.ActiveSheet.Cells(currRow, 8) = Workbooks(sourceFile).Sheets(“001-New Acct Approval”).Cells(8, 8)
    43. currRow = currRow + 1
    44. Workbooks(sourcebook).Close
    45. End If
    46. Next
    47. endtime = Now
    48. MsgBox “Merge started at ” & Format(starttime, “mmm d hh:mm”) & Chr(13) & “Merge completed at ” & Format(endtime, “mmm d hh:mm”), vbOKOnly, “Merging Complete”
    53. End Sub

    o Another response noted the following code to complete the same routine more quickly:
    1. Code:
    2. Sub test()
    4. Const sFilePath As String = “S:\Wealth Management\UWM Committee\Spreadsheet Import\Form 001 – New Account Approval” ‘change to suit your set up
    5. Const sSheetName As String = “Sheet1” ‘change to suit your set up
    7. Dim i As Long, j As Long
    8. Dim sFileName As String, sArg As String
    9. Dim ar(1 To 10000, 1 To 8)
    10. Dim mycells As Variant
    12. mycells = Array(“H9”, “M51”, “D32”, “G14”, “A6”, “C10”, “H10”, “H8”)
    14. sFileName = Dir(sFilePath & “\*.xls”)
    15. Do While Len(sFileName)
    16. i = i + 1
    17. sArg = “‘” & sFilePath & “\[” & sFileName & “]” & sSheetName & “‘!”
    18. For j = LBound(mycells) To UBound(mycells)
    19. ar(i, j + 1) = ExecuteExcel4Macro(sArg & Range(mycells(j)).Address(, , xlR1C1))
    20. Next j
    21. sFileName = Dir()
    22. Loop
    23. Range(“A6”).Resize(i, 8).Value = ar
    End Sub

    Again, thank you so much for your time and any assistance you provide.

    Best Regards,
    L. Taylor

  28. ronron

    Sir I would like to create data entry for ID, Full Name, Sur Name, Address, Department
    these data entry anyway to update the name, address or department using the existing ID.
    Please help thank you.

  29. Wendy Jordan

    I have made an Invoice form using excel and each invoice has its own tab. I want to extract certain information form these forms into another excel spreadsheet but the problem I have is that some of the cells are merged. The other problem I have is that the details are not just in the same row to be extracted it could be in the row several below for the same information. Please do you think you can help with this? I would most appreciate it. If you require more information let me know. Thank you

  30. Riz Momin


    I have lots of excel file in folder “C:\Temp\”
    All files have extension with .xls or .xlsm
    All these files (Excel Workbooks) are password protected by say “ABCD”

    I need a VB Code to do following:
    I want to change all password from “ABCD” to “1234”

    Let me know if you have any questions and appreciate your expert help with this.

  31. Shruthi

    How do i create an excel macro to update data automatically from a survey form?
    Also, how do i retrieve the data from excel when i need it?

  32. adventurer

    Hi sir,

    I am working on the data with 50+ number of columns. I am making two charts each for alternate set of columns (my columns consist of actual and % of total value alternatively). The charts are made properly until the number of columns plotted are 17 or less. The time when I enter i value for FOR loop for colums greater than 34(17 alternate columns), it starts showing ‘Series Formula Too Long Error’. I have tried running the code in different versions of excel, but the error is been shown in all of them. Why is this error showing? And what is the solution for it? You can find my sample data here. I have 23 departments.

    The following is my code.

    Sub TwoCharts()
    Dim rChart1 As Range
    Dim rChart2 As Range
    Dim iColumn As Long

    Dim cht1 As Chart
    Dim cht2 As Chart

    Const StrtRow As Long = 1
    Const EndRow As Long = 6

    With ActiveSheet

    Set rChart1 = .Range(.Cells(StrtRow, “B”), .Cells(EndRow, “B”))
    Set rChart2 = .Range(.Cells(StrtRow, “B”), .Cells(EndRow, “B”))

    For iColumn = 3 To 9 Step 2
    Set rChart1 = Union(rChart1, .Range(.Cells(StrtRow, iColumn), .Cells(EndRow, iColumn)))

    For iColumn = 4 To 10 Step 2
    Set rChart2 = Union(rChart2, .Range(.Cells(StrtRow, iColumn), .Cells(EndRow, iColumn)))

    Set cht1 = .Shapes.AddChart.Chart
    Set cht2 = .Shapes.AddChart.Chart

    With cht1
    .Parent.Left = .Parent.Left – .Parent.Width / 2
    .ChartType = xlColumnClustered
    .SetSourceData rChart1
    End With

    With cht2
    .Parent.Left = .Parent.Left + .Parent.Width / 2
    .ChartType = xlColumnClustered
    .SetSourceData rChart2
    End With

    End With
    End Sub

  33. Audrey Dowling

    Hello. I recently watched your video “Transfer data from one Excel worksheet to another automatically”. I found it useful, however I have one question. How do you use VBA to automatically transfer data whenever you type it in, without having to press a command button?

  34. Venice


    As an extension to your “How to transfer multiple rows of data from multiple workbooks into master workbook with VBA” tutorial, what if I wanted to overwrite the data based on a cell reference in the first column?

    For example, using Item name as the reference, populate and overwrite columns “quantity”, “price” and “weight” if the reference intersects.

  35. kamal.G

    I actually work on data which are to be updated on day by day process, where the current assets gets an up gradation or else gets damaged.” I Need the date and time of when i have changed the data and which data has been changed on specific” I would be happy if could get a best solution for the same.

  36. Vinod

    Sir,I am working at Kerala Govt.Service and a subscriber of your videos.Very thanks for the valuable lessons.I have a query about excel , that is as follows..
    I have created a macro enabled excel 2007 workbook and  saved it as 97-2003 workbook. When I open it in other system having 97-2003 version the previous page setup get changes .What can I do to keep the same page settings in different versions ? with thanks,
    Vinod.mob. +919744942923

  37. Latha

    Actually i’m doing automation project in vba actually i need to fetch the start and date based on the given price value. for example, consider the $ 39.99 value it is merged in range A4:D4 need to copy and paste in another sheet, as well as fetch the start date as 8/09/2015 and end date as 11/09/2015, date will be given in column wise like A2:D2 i need copy and paste in another sheet.This need to be done based on only the given price not directly. Can someone help me on this. Thanks in advance.

  38. Uttam

    Dear Sir,

    I visited your video Tutorial regarding “How to send email reminder automatically from excel worksheet using VBA.

    I am not getting reminder for all the mail ID’s. its working only for the first row mail id.

    Please suggest what to do.

    I have used the Below Codes.

    For Sheet:

    Private Sub Workbook_Open()
    For Each Cell In Range(“B2:B100”)
    If Cell.Value < Date + 3 And Cell.Value “” Then
    Cell.Interior.ColorIndex = 3
    Cell.Font.ColorIndex = 2
    Cell.Font.Bold = True
    Application.Speech.Speak (“send reminder to “)
    ‘cell.value = cell.offset (0, -1).value
    Application.Speech.Speak (Cell.Offset(0, -1).Value)
    End If

    ‘ Application.speech.speak (“Cell.text”)

    End Sub

    For Module:

    Sub sendRemindermail()
    Dim OutLookApp As Object
    Dim outLookMailItem As Object
    Dim iCounter As Integer
    Dim MailDest As String

    Set OutLookApp = CreateObject(“OutLook.application”)
    Set outLookMailItem = OutLookApp.CreateItem(0)

    With outLookMailItem
    MailDest = “”
    For iCounter = 1 To WorksheetFunction.CountA(Columns(4))
    If MailDest = “” And Cells(iCounter, 4).Offset(0, -1) = “Send Reminder” Then
    MailDest = Cells(iCounter, 4).Value
    ElseIf MailDest “” And Cells(iCounter, 4).Offset(0, -1) = “Send Reminder” Then
    MailDest = MailDest & “;” & Cells(iCounter, 4).Value
    End If
    Next iCounter

    .BCC = MailDest
    .Subject = “FYI”
    .Body = “Reminder: Your task is pending from last 15 Days.”
    End With
    Set outLookMailItem = Nothing
    Set OutLookApp = Nothing
    End Sub

    Waiting for your positive response.


  39. Mahesh Dabhade

    sir today being sunday and i was relaxing at home, i happened to go through your this web site. I found this website very informative. your videos are too good. your explanation is simple and very easy to understand. I really wonder how much time you must have invested doing this and respect your hardships.

    Thanks a lot


  40. Joel

    I have created the following code but I’m receiving and error “Run-time error ‘6’: Overflow” and I’m not sure how to correct the issue.

    Sub LoopThroughDirectory()
    Dim MyFile As String
    Dim erow
    Dim Filepath As String
    Filepath = “f / Users / jbrennecke / Desktop / PROJECTS / Newcompose / Index / originals / p1”
    MyFile = Dir(Filepath)
    Do While Len(MyFile) > 0
    If MyFile = “beauty – wellness.xlsx” Then
    Exit Sub
    End If

    Workbooks.Open (Filepath & MyFile)

    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ActiveSheet.Paste Destination:=Worksheets(“sheet1”).Range(Cells(erow, 1), Cells(erow, 4))

    MyFile = Dir

    End Sub

  41. Michael

    Hello Dr. I have been watching many of your videos and find them very informative. However, I still cannot seem to get my code to work properly. I have a master workbook which is used as a dashboard. I contains several worksheets and I have an xlsx file that I download weekly and would like to copy and paste the information from that worksheet into the master workbook. I used the following code based on information I learned from your website but for some reason it is not working. Would you mind looking this over for me and letting me know where I may have went wrong? Thanks you so much for your help and please continue to produce your helpful videos.

    Sub LoopThroughDirectory()
    Dim MyFile As String
    Dim erow
    MyFile = Dir(“C:\AMMO\forecasts\”)

    Do While Len(MyFile) > 0
    If MyFile = “DIV AMMO DASHBOARD.xlsm” Then
    Exit Sub
    End If

    Workbooks.Open (MyFile)

    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ActiveSheet.Paste Destination:=Worksheets(“Forecasts”).Range(Cells(erow, 1), Cells(erow, 9))

    MyFile = Dir


    End Sub

    Michael Molina

  42. Earnest


    could you please advise me if there is any way to update a cell value (date)in excel workbook without opening it. I have time sheets files saved in a folder. every week I need to open it one by one and update the date field which automatically change the data in the sheet.

  43. Nikhil Ranjan

    Dear Sir,

    I am learner of VBA , and your videos are very helpful for me . Most of the thing I learned only from your videos.
    Currently I am working on a project, where I have to extract useful information from raw data which is in sheet1. and I have to extract the information and paste it in Sheet2 and sheet3.

    Raw data is like:
    Date of completion, project ID, Project status, Project pending days, Project Analyst,

    these are the header of the raw data, I have to extract the information in sheet2, when project sheet1 consist “project not started ” and other similar type of status under Project status and Project pending days are >=90 days.

    I try to complete it with the help of you videos. but I always sucked.

    Kindly help me to complete it .
    Thanks in advance.

  44. Raaj

    How to Copy Single name from one excel sheet & paste it 10 or 20 times to another excel sheet

    what will be excel vba code for it.

  45. Rajesh

    Hi Sir,
    I have a query about VBA Data Entry form, can you please share with me your email id, so i can send you demo excel sheet.
    Awaiting for your response.
    Thank in advance,

  46. Jagadish Kumar Athikamsetty

    Hi Dinesh Kumar Takyar Sir,
    I hope your doing good. My self Jagadish Kumar.
    I’m so sorry to trouble you but I have started learning VBA(MACROS) Programming from few days back, Where I got much interest to continue my work on learning. So, from three days i’m working on program (TRIGGER A VBA CODE FOR CHECKING USERNAME FUNCTION MUST BE SENT FIRST BEFORE SENDING PASSWORD TO ANY USER). Here I’m attaching my code upto my level of knowledge.

    Private Sub CommandButton8_Click() ‘ Calls Password Function
    If Worksheets(“Sheet3”).Range(“G2”) = “” Then
    MsgBox “Add Study(XML) and Send Password ”
    If CommandButton7.Send.Select Then ‘ CommandButton7 is for Username Function
    Call password.EmailPassword ‘ This Password is Module and EmailPassword is Sub Function In that module
    MsgBox “Send UserName Method First and then Password.”
    End If
    End If
    End Sub

    Do you think the code can help anything and All my data I kept them in My Sheet2 and I have created these Buttons in Sheet1.

    Many Thanks

  47. Dibyendu Mazumder


    I am a Project Manger of a Company, I am having multiple Projects and each projects with a seperate serial Number as project number.

    I want to create a Excel Book where I can record my Project Names in a sheet and whenever I add a project title the same number sheet to be created. Like that 2000 + projects to be created.

    After creation of the sheets I want to combine the value into a Master Sheet.

    Kindly guide me with the program as I am the beginer of VBA excel.

    Looking for earliest response regarding a solutions.


  48. veerender

    Dear Sir,

    Could you please help me in correct the below vba

    Sub SendreminderMail()

    Dim OutlookApp As Object
    Dim OutLookMailitem As Object
    Dim iCounter As Integer
    Dim MailDest As String

    Set OutlookApp = CreateObject(“OutLook.application”)
    Set OutLookMailitem = OutlookApp.CreateItem(0)

    With OutLookMailitem
    MailDestination = “”
    For iCounter = 1 To WorksheetFunction.CountA(Columns(4))
    If MailDest = “” And Cells(iCounter, 4).Offset(0, -1) = “Send Reminder” Then
    MailDest = Cells(iCounter, 4).Value
    ElseIf MailDest “” And Cells(iCounter, 4).Offset(0, -1) = “Send Reminder” Then
    MailDest = MailDest & “;” & Cells(iCounter, 4).Value
    End If
    Next iCounter
    .BCC = MailDest
    .Subject = “missing”
    .Body = “Reminder”

    End With
    Set OutLookMailitem = Nothing
    Set OutlookApp = Nothing
    End Sub

  49. Raj Singh

    Hi Sir,
    I was watching one of your vdo “VLOOKUP Using VBA” but I would like you to kindly share a vdo how can i do vlookup from a different sheet to my master sheet.
    I am having a workbook named Salesman Data ( Where reqd fields are Code, Name, Business Amount) are there in 60 different worksheets. All the worksheets are having same no of columns but different rows.

    I am having another workbook where everyday I need to download a dump containing ( Code, Name, Business Amount ). ( Codes are common in both the sheets )

    So I need to update Salesman Data everyday from 2nd workbook. It is taking time to update the same everyday

    Kindly help in this matter.

  50. amit jain

    dear sir ,
    m very confuesd about loop ,
    kindly help me making a loop ,
    just like this

    UP32CN5149 PB08BU9467
    UP32CN6754 PB08CU9468
    UP32CN1506 PB08CZ9521
    UP32BN6482 PB08CB6227
    UP32CN6721 PB08CZ9522
    UP32BN7253 PB08CP2286

    if one company name in a cell a2 = Amba construction ltd.
    loop its (truck no.) value in a3
    its truck no .

    if the company name in a cell a2= bhogi transport
    loop its (truck no.) value in a cell a3

  51. Tom Phillips

    I’m closing the excel application after sending an excel file to email address. The excel message box asks if i want to Save or Dont Save. The in in Dont Save is underlined. How can i make that keystroke in VBA

  52. Tom Phillips

    Private Sub cmdsubmit_Click()
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range(“A2”).Value = txtlast
    Range(“B2”).Value = txtfirst
    Range(“C2”).Value = txtmail
    Range(“D2”).Value = Lstbox
    txtlast.Text = “”
    txtfirst.Text = “”
    txtmail.Text = “”
    ActiveWorkbook.SendMail “Thomas.Phillips@jfs.ohio.gov”, “My email contact information” & Date

    Unload Me

  53. Abdul aziz

    Dear sir
    Thank you very much for your hrlpful videoes. Now i want to know from you that, how can i find specific data and delete that data or record or row by using excel vba user form.
    Please help me.

  54. Abdul Rehman Abbasi

    Hello sir,
    I watched lot of your videos and learned a lot from you.
    After learning i have designed a user form in which i have finalized every step just the last step is confusing me a lot.

    I have a Command Button named “cmdNew” in my user form..
    Whenever i click this button, i need the whole user form to be blank(which i have done) and in the first text box named “DC” i need auto DC number to be shown in text box analyzing from value of column A in last row (means last DC Number + 1 ).

    Your valuable guidance needed.

    If you want i can send you the workbook.

  55. Justin

    Sir I am creating a test certificate template, where in it need to save the date from the template to worksheet and bring back the date to the template using the button function.
    button like save, search, print and export to pdf.


    Sir, i would like to download the live share price from BSE and NSE website for different scrip with different time frame, how to create the same.

  57. Nga

    Thank u so much different vba codes
    Please help with below problem.
    I have one excel file which is used by multiple people let say employee , when they fill and submit I will get a mail, but it’s not recording any where. After I need to copy that mail to excel but my question is when submit I should get mail as well as it’s update in shared excel file Without opening shared excel file, one by one user….and also I should not face data traffic issues
    Could you please help in this with vba code.

  58. Suraj Nepali

    Dear Sir,

    I hope This Comment Get you Fine, I have 1 question ….. how to transfer data in different sheets form master sheet using Vba

  59. Yousuf

    Hello sir.i want the code for the video posted by you on YouTube for Micromax Mobile rates from amazon.in
    Please give me the total code


Leave a Reply

Your email address will not be published. Required fields are marked *