Excel VBA

117 thoughts on “Excel VBA”

  1. 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. 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. 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. 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. 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. Hello Sir.
            please help me in creating macro for coping a heading and calculating worksheets

          3. HI There

            I would like to ask how to save a userform associated with a spreadsheet or a workbook?

            I have made a couple of userform before but every time I open it the next day, it’s not been save.

            Please help. Thank you

      2. 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!

      3. Dear Mr. Takyar,

        Please help me sir. Let say i have a server. The server has many file in excel type. When i want to enter to the server, i have to write down the IP of the server let say, then enter username and password. After that the location of the file let say\data….. In folder “data” there are so many excel that contain performance sales data (i.e. date, product type, sales total of product) every day.

        What i want to do, i want to make an excel VBA that when i clicked the button, the performance sales will be copy become a report in one excel. Please help me with my problem or please give me reference if you have already fixed this problem. Thank you so much Sir

        Best Regards,


  4. 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. 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. 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. 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.

  7. 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.

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

  8. 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

  9. 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. 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. 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.

  10. 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. “\\\pag\OffShore PAG\Parallel Run\Reports\Sunil\Mymacros\sample
      should be?
      “\\pag\OffShore PAG\Parallel Run\Reports\Sunil\Mymacros\sample

  11. 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

  12. 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 ?

  13. 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!

  14. 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.

  15. 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

  16. 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


  17. 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

  18. 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.


  19. 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.

  20. 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.

  21. 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

  22. 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 .


  23. 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.

  24. 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

  25. 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.


  26. 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

  27. 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.

  28. 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

  29. Hello:

    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.

  30. 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?

  31. 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

  32. 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?

  33. Hi,

    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.

  34. Hi,
    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.

  35. 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

  36. 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.

  37. 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.


  38. 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


  39. Hello,
    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

  40. 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

  41. Hi,

    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.

  42. 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.

  43. Hi
    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.

  44. 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,

  45. 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

  46. Hello,

    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.


  47. 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

  48. 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.

  49. 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

  50. 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

  51. 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

  52. 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.

  53. 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.

  54. 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.

  55. 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.

  56. 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.

  57. 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

  58. 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

  59. Daarnaast kunt u rimpelvorming voorkomen met diverse behandelingen. Beste middel tegen striae vlog titangel.cremes.amsterdam. Britse vrouwen denken een wondermiddel ontdekt te hebben.

  60. Dear Danish
    I have seen a lot of trainings in your youtube channel and also google my issue but I couldn’t find a complete answer, so I’m posting it here hopping to find an answer please,

    I have a master file with 50 worksheets which contains couple photos, 10 columns and 31 rows. these work sheets has specific name like Bob, Marry and so on. I need to copy each sheet except specific range (or one column) to another workbook as value, with the same name and format and then each time that a data changes in the master file the other one can be updated accordingly.
    for example I need to have every thing except c10:c30 in one file named class1, or everything except d5:f31 should be copied to file class2. the class 1 and class 2 having the same data format (10 column and 31 rows and same photos) but the specified range should be empty there.

    know I’m doing this by making couple copies of the master file and then deleting the unnecessary parts from all worksheets and saving the workbook.
    but for each change I have to do this and it is time consuming.

    I believe that I need 2 VBA macros that do the following steps:
    first macro with a button named “copy to class1” does:
    1-copy the worksheets to a new file
    2-clear the contents of the specified range
    3-merge the specific cells (to have a better look for example if C10:C30 cleared, merges B10 & C10, B11& C11, & …B30& C30)
    4-save the file

    second macro should be assigned to the worksheet change and if any thing changed in a cell then copy it into the same cell of the file class1 and class2.

    could you please help me to do that?

    thank you in advance.

  61. Dear sir,

    I fixed sheet1 10 command button in excel sheet 10 rows. 1st command button click time 1st row paste to sheet2 1st row. next click paste to sheet2 second row how to enter the code.

  62. I fixed sheet1 10 command button in excel sheet 10 rows. 1st command button click time 1st row paste to sheet2 1st row. next click paste to sheet2 second row how to enter the code.

    Don’t use macros only vba code

  63. Dear Mr. Dinesh ,

    Thank you for all of your videos. I Google my issue on your you tube channel, but cannot find the answer. Could you please spare some time to help me with my task at work, which I am really struggling.
    I need to copy specific rows in select worksheets in several folders and paste it into master worksheet.
    1. Folders: each department has one folder and the folder name is department’s name. I need to copy the name of department and paste it to master file at the first column.
    2. Worksheet: each folder have about 12 worksheets, each worksheet has different name, but I just need to copy the information from 6 or 8 worksheets only.
    3. Row: each worksheet have several categories. Each category has several rows, always start with category name and end at Category Total. They have the same columns titles like account, account name, vendor name, amount, etc. (column titles repeat in each category).
    4. The master file will need these information
    – Column A: Department name
    – Column B: Category name
    – Column C: all the columns and rows in work sheets, except the rows: “Category name” and “Category Total” (I will create pivot table base on master file).

    Greatly appreciate your help.

  64. Dear Sir,
    I have learnt VBA coding for excel only going through your online lessons. I have developed a Bill of materials builder on excel which is functional as of now. However, I have an issue in creating Named range using Name Manager through VBA. If I form the “ReferToR1C1” in a column of a sheet and paste it through VBA code, I get the content padded with double quote on either side and the name range is not getting referred. Kindly advise
    The code is
    Dim newrow As Long
    Dim A As String
    A = ActiveCell.Value
    newrow = Worksheets(“hsnk”).Range(“A” & Rows.Count).End(xlUp).Row
    ActiveWorkbook.Names.Add Name:=”Apples”, RefersToR1C1:=A
    ActiveWorkbook.Names(“Apples”).Comment = “”

  65. Hello Sir,
    I have 10 excel production files in a folder. daily basis productions will be updated on 10 columns in each file and it have unique ID’s like 481488.Xls.

    if i select from date,end date and unique ID in master file, the respective data are need in respective fields in master file. kindly help on this….

  66. Dear Dinesh Sir,

    First and foremost you are a great inspiration to me and thank you for make great videos.
    Sir, I have a dilemma whereby the company I work for has 5 different companies that use 5 different systems, 5 different customer account numbers. As we have the same customers purchasing from possibly 3-5 of these companies on a weekly basis, I wanted to create a vba code that can extract, create/ generate one group customer /debtor account number from 5 different aged debtor reports in order to do a vlookup of the debtors so I can report on the total exposure for each customer. With this in mind I would like the vba to also generate a new group customer/ debtor account number every time a new customer appears. The only commonalty between all of these customers are either a VAT number or the owners ID number.
    I’m really hoping you can assist and help create a code for me that I can use

    Waiting in anticipation from your keen follower,


  67. I have 2 reference
    criteria 1 = cell “B2” (CBM,TBM,BDM) dropdown list
    criteria 2 = cell “C2” (WK_1 to WK_4) dropdown list
    columns WK_1 or WK_4 number added that time VBA running
    but how to VBA coding type and auto change Wk_1 to WK_4 on dropdown list

    Columns A Columns B Columns C Columns D
    WK_1 WK_2 WK_3 WK_4


    please help Thanks sir

  68. Dear Takyar Sir
    I have learn sow math from your tutorial you are the best and i need your help i am coping some Greek words from a textbox to bodymail bat i need a encoder to utf-8 i will be happy to read news from you thank you very mach

  69. Hello,

    I have command button on the main sheet and hyperlinked with other sheets. How can i hide all the sheets except the main sheet and still it can be hyperlinked ?

    I am waiting for your reply

    Thank you

  70. Hello, thank you for your macro which I have copied. When pulling in my data, I get all the correct information up to column I (this column has no data as well as column J, K and L) then the information comes back but in the wrong columns as this should appear in column I.
    Does this have anything to do with the references to columns i and j in the macro?

    Sub getWordFormData()
    Dim wdApp As New Word.Application
    Dim myDoc As Word.Document
    Dim CCtl As Word.ContentControl
    Dim myFolder As String, strFile As String
    Dim myWkSht As Worksheet, i As Long, j As Long

    myFolder = “C:\Users\jhiggins\OneDrive – Laureate Education – AUS\General\HDR Forms”
    Application.ScreenUpdating = False

    If myFolder = “” Then Exit Sub
    Set myWkSht = ActiveSheet
    Range(“A1”) = “Title”
    Range(“a1”).Font.Bold = True
    Range(“B1”) = “First Name”
    Range(“B1”).Font.Bold = True
    Range(“c1”) = “Surname”
    Range(“c1”).Font.Bold = True
    Range(“D1”) = “Phone”
    Range(“D1”).Font.Bold = True
    Range(“e1”) = “Email”
    Range(“e1”).Font.Bold = True
    Range(“f1”) = “Major area of expertise 1”
    Range(“f1”).Font.Bold = True
    Range(“g1”) = “Major area of expertise 2”
    Range(“g1”).Font.Bold = True
    Range(“h1”) = “Major area of expertise 3”
    Range(“h1”).Font.Bold = True
    Range(“I1”) = “TUA Staff ID”
    Range(“I1”).Font.Bold = True
    Range(“j1”) = “Vertical”
    Range(“j1”).Font.Bold = True
    Range(“k1”) = “Employment Type”
    Range(“k1”).Font.Bold = True
    Range(“L1”) = “End Date”
    Range(“L1”).Font.Bold = True
    Range(“m1”) = “Current Employer”
    Range(“m1”).Font.Bold = True
    Range(“n1”) = “Current Position”
    Range(“n1”).Font.Bold = True
    Range(“O1”) = “Employer Address”
    Range(“O1”).Font.Bold = True
    Range(“Q1”) = “Highest Qualification”
    Range(“Q1”).Font.Bold = True
    Range(“R1”) = “Other qualification”
    Range(“R1”).Font.Bold = True
    Range(“S1”) = “Recorded with HR?”
    Range(“S1”).Font.Bold = True
    Range(“t1”) = “Updating HR? ”
    Range(“t1”).Font.Bold = True
    Range(“u1”) = “Previous Supervision?”
    Range(“u1”).Font.Bold = True
    Range(“v1”) = “Details of Previous Supervision”
    Range(“v1”).Font.Bold = True
    Range(“w1”) = “Number of PhD students supervised”
    Range(“w1”).Font.Bold = True
    Range(“x1”) = “Number of MPhil students supervised”
    Range(“x1”).Font.Bold = True
    Range(“y1”) = “Number of Honours students supervised”
    Range(“y1”).Font.Bold = True
    Range(“z1”) = “Number of PhD students graduated”
    Range(“z1”).Font.Bold = True
    Range(“aa1”) = “Number of MPhil students graduated”
    Range(“aa1”).Font.Bold = True
    Range(“ab1”) = “Number of Honours students graduated”
    Range(“ab1”).Font.Bold = True

    i = myWkSht.Cells(myWkSht.Rows.Count, 1).End(xlUp).Row
    strFile = Dir(myFolder & “\*.docm”, vbNormal)

    While strFile “”
    i = i + 1

    Set myDoc = wdApp.Documents.Open(Filename:=myFolder & “\” & strFile, AddToRecentFiles:=False, Visible:=False)

    With myDoc
    j = 0
    For Each CCtrl In .ContentControls
    j = j + 1
    myWkSht.Cells(i, j) = CCtrl.Range.Text
    End With
    myDoc.Close SaveChanges:=False
    strFile = Dir()
    Set myDoc = Nothing: Set wdApp = Nothing: Set myWkSht = Nothing
    Application.ScreenUpdating = True
    End Sub

  71. hi , I have Onepagertab with 1 pager summary of 100 projects listed on the consolidated tab. On Onepagertab i have a cell that i can change like 1, 2, 3 to 100 and pulls one pager sumary from consolidate.

    Problem is I have to print 100 1 pagers and I have to create 100 sheets so I can print as workbook. Do not know how i can do that. any help will be appreicated . For all 100 projects i need 100 tabs with names.

  72. Hello, Thank you for your change tracker code.
    It works well, but I was wondering if you could help me modify it so that it not only logs the changes but inserts a oval ring around Each cell that changed. I have a piece of code that adds the oval ring to a Selected Cell (by clicking on it):

    Sub DrawCircle()
    Dim Arng As Range
    Dim WorkRng As Range
    Set WorkRng = ActiveSheet.Name
    For Each Arng In WorkRng.Areas
    With Arng
    x = Arng.Height * 0.1
    y = Arng.Width * 0.1
    Application.ActiveSheet.Ovals.Add Top:=.Top – x, Left:=.Left – y, _
    Height:=.Height + 1.5 * x, Width:=.Width + 1.5 * y
    With Application.ActiveSheet.Ovals(ActiveSheet.Ovals.Count)
    .Interior.ColorIndex = xlNone
    .ShapeRange.Line.Weight = 5
    End With
    End With
    End Sub

    How can I modify your already existing tracker to pass in the cells and sheets that this oval inserting code needs. Also how can I make it so the cells that get circled are the cells that changed within that current week. In other words I don’t want any changes older than a week to be circled.

    Thank you for your time and help.

  73. Hi,
    I have two Excel sheets A and B. I need to copy contents of cells from Excel A to Excel B in the daily basis. Excel B cells having some formula. While copy and paste, the required contents pasted in the right place(in a row as expected) , but the other empty cells below the same row (Expected to update on next day) having formulas displaying REF!. It looks ugly . Can some body helps me how to remove REF! with out changing the formulas in the cell.

  74. Hello Deepak Sir,
    I saw your youtube video on how to save an excel with file name as one of its cell data.
    I have a querry to know how to save the next few sheets (like Sheet1, Sheet2, Sheet3…Etc) with file extension names as their cell data…Thanks

  75. Hi,
    I have created a cash book with multiple columns where each column represent a income of a particular property. I now want to create multiple ledgers from this entry sheet where each entry in on the ledger is posted to the next empty space. Each ledger will copy over the date, description and amount from the cash book
    is this possible?

  76. Hi

    I have created a spreadsheet to show when service charges are due for each property we manage. I have tried following your videos in order to create a macro that sends an email when the service charge is due (highlighted in red), but I can’t figure it out! Can you help me?

  77. Dear Mr. Takyar,
    I have listen to and searched for the ideal online ‘Tutor’ on the web. No one comes close to you. Your relaxed pace of instruction is what makes you the ‘perfect’ teacher. Keep it up Mr. Takyar as you are doing it ‘right’.

    On another subject, cold you please advise on my simple yet crude problem: I enter as well as update item prices on a Userform on which there are 4 txtboxes, The 1st txtbox finds the item (description and price as well as stock available).
    What I am facing is that when I update a price which is let’s say $ 10 I wish for the txtbox (txtiprice) to add 2 zeros at the end so when write to cell the number is reflecting the decimal as well.
    It is driving me crazy and I do need help.
    I will appreciate any guidance you may give me on this.

  78. dear Sir ,
    i have a listbox for listing my sheet table data , i need to edit the data from listbox(double click) by using another form can you please help me to do this

  79. Dear Sir,
    I have excels with 21 columns. I define multiuser for it with their own password. Also, I have a code for auto-sorting it. but I have to use protect the sheet, protect workbook and protected sheet and shared it. My code doesn’t work. What should am I do?
    Also, I want to cut some rows due to a specific word in one column to another workbook. I can only cut it to another sheet in that workbook. Can you help me with this?
    My code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Unprotect Password:=”6070″
    If Target.Column 11 Or Target.Columns.Count > 1 Then _
    If Target.Column 13 Or Target.Columns.Count > 1 Then _
    If Target.Column 14 Or Target.Columns.Count > 1 Then _
    Exit Sub
    Dim tmp As Variant
    tmp = Cells(Target.Row, 21).Formula ‘save contents
    On Error GoTo Enable_Events
    Application.EnableEvents = False
    Cells(Target.Row, 21) = “#$”
    Range(“K1”).Sort Key1:=Range(“K1”), Order1:=xlAscending, Header:=xlYes
    Cells(Application.Match(“#$”, Columns(21), 0), 11).Select
    Range(“M1”).Sort Key1:=Range(“M1”), Order1:=xlAscending, Header:=xlYes
    Cells(Application.Match(“#$”, Columns(21), 0), 13).Select
    Range(“N1”).Sort Key1:=Range(“N1”), Order1:=xlAscending, Header:=xlYes
    Cells(Application.Match(“#$”, Columns(21), 0), 14).Select
    Cells(Selection.Row, 21) = tmp ‘restore contents
    Application.EnableEvents = True

    If Target = “ارسال” Then
    Target.EntireRow.Cut Sheets(“Sheet4”).Cells(Rows.Count, “A”).End(xlUp).Offset(1, 0)
    End If
    ActiveSheet.Protect Password:=”6070″

    End Sub


  80. Hi Dinesh,

    As discussed over the call, can you please help me with a vba code for uploading a outlook email in share point through automatically.

    Kindly share the vba code if you, kindly help me in this.


  81. Hi, Your videos have been very helpful for me to set up a database with a VBA user form.
    I have 2 question for you.
    I have a VBA user form that generates a work repair ticket for new homes and a Word document with a list of repairs/faults.
    What I would like to happen, is if I click on the ticket number it would show the list of repairs/faults.
    Many thanks if you can help
    Once a new ticket is generated, is it possible to get an email notification email sent out.

  82. Dear Dinesh, having watched many of your very helpful tutorials online for some time now and getting some assistance from you once before I have learned a lot of very useful tasks, for which I thank you.

    I am currently trying to create a sales and inventory log to simplify a multiple data entry process so that the data can be entered once in one place on a master entry sheet, then transfers this information into two different sheets in different ways so the information is transferred into one as each complete transaction and another sheet as the breakdown of that transaction as individual entries of each item one by one for stock and ordering control at the same time.

    I would like to ask your assistance with this piece of VBA code, it currently works but is very clunky and causes screens to flash as the code runs. I am not very experienced in VBA and as you will probably see I have probably over stepped my ability on this task so really need some help, if you could spare some time to look over it for me.

    Although I placed Application.ScreenUpdating = False into the beginning of the script, this does not seem to have made any difference to the visible sheet flashing as the loop runs through the various cells picking up data.

    I wonder if you could help with this as I have tried everything I can think of and I’m now of the opinion that it must just be the way in which I have written the code that is causing the functionality to be quite so basic and clunky.

    As mentioned when the code is activated through the command button the functionality works but it’s probably just written poorly and therefore causing problems.

    I would be very grateful if you would please take a look at what I have put together and give some guidance as to how to make this work properly and smoothly.

    Please let me show my appreciation in advance as I know your wealth of experience will probably find my code somewhat primitive and backward. I also know you will have the ability to solve the issues, so thank you for your help.

    Many thanks

    Please see below:

    Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    Dim InvoiceDate As String
    Dim InvoiceRef As String
    Dim Supplier As String
    Dim FirstName As String
    Dim MiddleName As String
    Dim LastName As String
    Dim ClientRef As String
    Dim TransType As String
    Dim XeroID As String
    Dim Wine As String
    Dim WineRef As String
    Dim Year As String
    Dim Status As String
    Dim Format As String
    Dim CasePrice As Single
    Dim Cases As Single
    Dim Total As Single
    Dim myData As Workbook
    Dim columninteger As Integer
    columninteger = 9


    InvoiceDate = Range(“E4”)
    InvoiceRef = Range(“E5”)
    Supplier = Range(“E6”)
    FirstName = Range(“I3”)
    MiddleName = Range(“I4”)
    LastName = Range(“I5”)
    ClientRef = Range(“I6”)
    TransType = Range(“J4”)
    XeroID = Range(“J6”)

    Wine = Range(“C” + CStr(columninteger))
    WineRef = Range(“D” + CStr(columninteger))
    Year = Range(“E” + CStr(columninteger))
    Status = Range(“F” + CStr(columninteger))
    Format = Range(“G” + CStr(columninteger))
    CasePrice = Range(“J” + CStr(columninteger))
    Cases = Range(“K” + CStr(columninteger))
    Total = Range(“L” + CStr(columninteger))

    ActiveWorkbook.Worksheets(“Captured2″).Visible = True

    ActiveSheet.Unprotect Password:=”CPL”
    RowCount = Worksheets(“Captured2”).Range(“A1”).CurrentRegion.Rows.Count

    If Not Wine = “” Then

    With Worksheets(“Captured2”).Range(“A1”)
    .Offset(RowCount, 0) = InvoiceDate
    .Offset(RowCount, 1) = XeroID
    .Offset(RowCount, 2) = TransType
    .Offset(RowCount, 3) = InvoiceRef
    .Offset(RowCount, 4) = Supplier
    .Offset(RowCount, 5) = FirstName
    .Offset(RowCount, 6) = MiddleName
    .Offset(RowCount, 7) = LastName
    .Offset(RowCount, 8) = ClientRef
    .Offset(RowCount, 9) = Wine
    .Offset(RowCount, 10) = WineRef
    .Offset(RowCount, 11) = Year
    .Offset(RowCount, 12) = Format
    .Offset(RowCount, 13) = Status
    .Offset(RowCount, 14) = CasePrice
    .Offset(RowCount, 15) = Cases
    .Offset(RowCount, 16) = Total

    If columninteger = 33 Then
    .Offset(RowCount, 17) = Range(“L34”)
    End If

    End With

    With Worksheets(“Captured2”).Range(“A1”)
    .Offset(RowCount – 1, 17) = Range(“L34”)
    End With

    End If

    Call TransactionLoop(InvoiceDate, XeroID, TransType, InvoiceRef, Supplier, FirstName, MiddleName, LastName, ClientRef, Wine, WineRef, Year, Format, Status, CasePrice, Cases, Total)

    columninteger = columninteger + 1
    Loop Until columninteger = 34


    ActiveWindow.SelectedSheets.Visible = False
    End Sub

    Private Sub TransactionLoop(DateData As String, XeroID As String, TransType As String, InvoiceRef As String, Supplier As String, FirstName As String, MiddleName As String, LastName As String, ClientRef As String, Wine As String, WineRef As String, Year As String, Format As String, Status As String, CasePrice As Single, Cases As Single, Total As Single)

    If Cases > 0 Then

    Sheets(“Stock Ledger”).Select
    Sheets(“Stock Ledger”).Activate
    Sheets(“Stock Ledger”).Range(“A4”).Select

    Dim activeRow As Integer

    Dim loopID As Single
    loopID = 0

    activeRow = 0

    Sheets(“Stock Ledger”).Range(“A4”).Select

    ActiveCell.Offset(1, 0).Activate
    activeRow = activeRow + 1
    Loop Until ActiveCell.Text = “”

    With Worksheets(“Stock Ledger”).Range(“A4”)
    .Offset(activeRow, 0) = DateData
    .Offset(activeRow, 1) = XeroID
    .Offset(activeRow, 2) = TransType
    .Offset(activeRow, 3) = InvoiceRef
    .Offset(activeRow, 4) = Supplier
    .Offset(activeRow, 5) = FirstName
    .Offset(activeRow, 6) = MiddleName
    .Offset(activeRow, 7) = LastName
    .Offset(activeRow, 9) = ClientRef
    .Offset(activeRow, 10) = WineRef
    .Offset(activeRow, 11) = Wine
    .Offset(activeRow, 12) = Year
    .Offset(activeRow, 13) = Format
    .Offset(activeRow, 14) = CasePrice
    .Offset(activeRow, 15) = 1
    .Offset(activeRow, 16) = CasePrice
    End With

    loopID = loopID + 1
    Loop Until loopID = Cases

    End If

    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
    Sheets(“Purchases”).Protect Password:=”CPL”

    End Sub

  83. This is the whole code for all the buttons to as explained by the guru of this website Dinesh.

    Option Explicit ‘form
    Dim currentRow As Long
    Dim i As Long

    Private Sub cmdAdd_Click()
    ‘add data in the new line and avoid duplicate data
    Dim lastRow As Long
    Dim i As Long
    Dim Count As Long

    lastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    lastRow = lastRow + 1
    Cells(lastRow, 1) = txtName
    Count = 0
    For i = 2 To lastRow
    If txtName = Cells(i, 1) Then
    Count = Count + 1
    End If
    If Count > 1 Then
    Cells(lastRow, 1) = “”
    Cells(lastRow, 2) = “”
    Cells(lastRow, 3) = “”
    MsgBox “Duplicate entry! Name already exists!”
    End If

    If Count = 1 Then
    Cells(lastRow, 1) = txtName.Text
    Cells(lastRow, 2) = txtGrade.Text
    Cells(lastRow, 3) = txtMarks.Value
    End If
    Next i

    txtName = “”
    txtGrade = “”
    txtMarks = “”

    End Sub

    Private Sub cmdClear_Click()
    txtName = “”
    txtGrade = “”
    txtMarks = “”

    ‘Dim ctl As Control
    ‘For Each ctl In Me.Controls
    ‘ If TypeName(ctl) = “Textbox” Then
    ‘ ctl.Value = “”
    ‘ End If
    ‘Next ctl

    End Sub

    Private Sub cmdClose_Click()

    Unload Me
    Application.Visible = True
    End Sub

    Private Sub cmdQuit_Click()
    End Sub

    Private Sub cmdSearch_Click()
    Dim totalRows As Long
    Dim i As Long

    totalRows = Worksheets(“Sheet1”).Range(“A1”).CurrentRegion.Rows.Count
    For i = 2 To totalRows
    If Trim(Sheet1.Cells(i, 1)) = Trim(txtName.Text) Then
    txtName.Text = Sheet1.Cells(i, 1)
    txtGrade.Text = Sheet1.Cells(i, 2)
    txtMarks.Text = Sheet1.Cells(i, 3)
    Exit For
    End If
    Next i
    End Sub

    Private Sub cmdPrevious_Click()
    If currentRow = 2 Then
    MsgBox “You are in the first row of the data!”
    Exit Sub
    End If

    currentRow = currentRow – 1
    txtName = Cells(currentRow, 1)
    txtGrade = Cells(currentRow, 2)
    txtMarks = Cells(currentRow, 3)
    End Sub

    Private Sub cmdDelete_Click()
    Dim Answer As Variant
    Answer = MsgBox(“Are you sure you wish to delete the record?”, vbYesNo + vbQuestion, “Delete Record?”)
    If Answer = vbYes Then
    Cells(currentRow, 1).EntireRow.Delete
    End If

    End Sub

    Private Sub cmdNext_Click()
    Dim lastRow As Long
    lastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

    If currentRow = lastRow Then
    MsgBox “You are in the last row! No more data!”
    Exit Sub
    End If

    currentRow = currentRow + 1
    txtName = Cells(currentRow, 1)
    txtGrade = Cells(currentRow, 2)
    txtMarks = Cells(currentRow, 3)

    End Sub

    Private Sub cmdUpdate_Click()
    Dim Answer As Variant

    Answer = MsgBox(“Are you sure you want to update the record?”, vbYesNo + vbQuestion, “Update Record”)
    If Answer = vbYes Then
    Cells(currentRow, 1) = txtName.Text
    Cells(currentRow, 2) = txtGrade.Text
    Cells(currentRow, 3) = txtMarks.Value
    End If
    End Sub

    Private Sub UserForm_Initialize()
    currentRow = 2
    txtName = Cells(currentRow, 1)
    txtGrade = Cells(currentRow, 2)
    txtMarks = Cells(currentRow, 3)
    End Sub

    Option Explicit ‘workbook

    Private Sub Workbook_Open()
    Application.Visible = False
    End Sub

  84. Sub Test1()

    Dim IE As Object
    ‘Dim Doc As HTMLDocument
    Set IE = CreateObject(“InternetExplorer.Application”)
    IE.Visible = True
    IE.Navigate “https://wss.mahadiscom.in/wss/wss?uiActionName=getNewConnectionRequest”
    Set Doc = IE.dOCUMENT

    Doc.GetElementByID(“ddlConsumerCategory”).Value = “1-LT-SUPPLY”
    Doc.GetElementByID(“ddlSupplyType”).Value = “SINGLE-SINGLE PHASE”
    Doc.GetElementByID(“ddlserviceRequested”).Value = “001-New Connection (Permanent)”
    Doc.GetElementByID(“ddlFirstName”).Value = “Mr”
    Doc.GetElementByID(“txtFirstName”).Value = “SRAVAN”
    Doc.GetElementByID(“txtMiddleName”).Value = “TARA”
    Doc.GetElementByID(“txtLastName”).Value = “AKHYE”
    Doc.GetElementByID(“ddlOccupation”).Value = “OTHERS”
    Doc.GetElementByID(“ddlApplicantClass”).Value = “4-ST”
    Doc.GetElementByID(“ddlGender”).Value = “1-Male”
    Doc.GetElementByID(“txtSupplyNearestConsumerNo”).Value = “068040001551”
    Doc.GetElementByID(“supplyPlot”).Value = “BORGAON”
    Doc.GetElementByID(“txtSupplyAddress1”).Value = “BORGAON”
    Doc.GetElementByID(“txtSupplyLandMark”).Value = “BORGAON”
    Doc.GetElementByID(“ddlDistrict”).Value = “NASHIK”
    Doc.GetElementByID(“ddlTaluka”).Value = “SURGANA”
    Doc.GetElementByID(“ddlVillage”).Value = “Borgaon”
    Doc.GetElementByID(“ddlPinCode”).Value = “422211-Borgaon B.O”
    Doc.GetElementByID(“txtMobileNumber”).Value = “7030137471”
    Doc.GetElementByID(“txtEmail”).Value = “sdo@gmail.com”
    Doc.GetElementByID(“ddlTypeOfPremises”).Value = “1-OWNED”
    Doc.GetElementByID(“ddlConsumerSubCategory”).Value = “40-Residential”
    Doc.GetElementByID(“txtPlace”).Value = “SURGANA”

    End Sub

  85. Dear Mr. Takyar,

    Please help me sir. Let say i have a server. The server has many file in excel type. When i want to enter to the server, i have to write down the IP of the server let say, then enter username and password. After that the location of the file let say\data….. In folder “data” there are so many excel that contain performance sales data (i.e. date, product type, sales total of product) every day.

    What i want to do, i want to make an excel VBA that when i clicked the button, the performance sales will be copy become a report in one excel. Please help me with my problem or please give me reference if you have already fixed this problem. Thank you so much Sir. My contact : jimmymetalurgi@gmail.com

    Best Regards,


  86. HI Sir,
    I have been watching a number of videos on YouTube to improve my excel skills. I am impressed with two individual for their style of explanation. First one is you as you have one of the simplest way of explaining things, with step by step instructions. It reminds me of my respected teachers from school days.

    I need your help with regards to extracting data from a pivot table.
    I have a table of people and their qualification (two columns).
    I created a simple pivot table using filter by qualification to get unique names.
    Now i want to get this name list from pivot table in user form and then create a check box list on the user form.
    How can i call the data of this pivot table in userform and count the rows to generate appropriate number of check boxes.

  87. Hi Sir,

    I watch most of ur tutorials in YouTube which helped me a lot in learning Excel VBA.

    Thanks for teachings.

    I have one query regarding the same
    At my office I found few Excel macros which are saved as .xlsx format.
    The moment I open the file it shows a notification saying loading …. With percentage.

    Could you please make a video on how an Excel macro saved as .xlsx is used to run the code.

    Is it possible with excel or should we need to use any other programming language to create as such.


  88. Hi ,Sir I am working on scraping a website using VBA, Where i am trying to use the information so as to automate my work related stuff. I am stuck at a stage where i am initiating a website to open the file upload screen. Once this screen pops out, My VBA code is frozen until the pop up window for file upload is closed. I could not able to upload the file , is ther a way i could use the VBA macro to provide the link of filepath i could paste in the file upload window and submit to the web site.

  89. Hi Dinesh,

    Could you do a video on a userform in Excel VBA that shows images much like a powerpoint slides.

    The image box on the userform must show images from a file saved on the desktop, whereby the image box displays the first image in the file for 30 seconds and then shown the second one for 30 seconds and so on.

    This must loop continuously repeating the showcase of all the images in the file


  90. Dear Mr. Takyar,

    I am a student and currently developing a database system for inventory management. Is there any possible way to prevent user from entering data in worksheet but only via userforms? the worksheets are locked but user can only enter data by filling the userforms.
    One more thing, I would like to make my excel macro enabled to be open by many users in one time. Kindly asking for your help on these. I would be grateful if you could provide me a link of tutorial video. Looking forward to hearing from you.

    Thank you Sir. Regards
    Zahin Zulkifli

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.