Get in touch

We would like to hear from you, whether it’s feedback about ANALYSISTABS Products or a request for any topic, please let us hear from you. We’ll answer your questions the best that we can and give you the information you need.

If you have any questions or any suggestion regarding our tutorials, please provide your valuable suggestions and feedback through email. If you are interested in knowing more about us please drop a mail and we are always happy to help you in solving your questions. You can email us directly or leave your comment in this page, depending on your needs.

How Can We Help?

We (NIRUKA TECHNOLOGIES LLP) know our work is about starting the conversations that matter to you and your business.

Email: info@analysistabs.com
Website: ANALYSISTABS.COM

  1. GK August 2, 2013 at 9:32 AM - Reply

    As an Excel Beginner i find easier to learn and practice. Thanks – Ganesh K

  2. PNRao August 3, 2013 at 10:05 PM - Reply

    Thank you Ganesh!

  3. mahendra August 27, 2013 at 12:57 AM - Reply

    I want to learn vba,excel sql

  4. PNRao August 30, 2013 at 9:50 AM - Reply

    Hi Mahendra,
    We have provided many topics for Excel VBA tutorials, you can learn from our site. And regarding SQL, we have only the basics required for Business Analyst here. Advanced SQL tutorials will be posted soon.

    Tutorials

    Thanks-PNRao

  5. stella November 15, 2013 at 2:48 PM - Reply

    Hello PNRao,
    I’m happy found your website today. I wonder if you could give real example/study case of excel/vba macro . Started with some data, and then manipulated in excel report using excel formula and/or vba macro codes. I think it will be easy for some people to see real life sample in action from beginning until finish.

    Thank you,Stella

  6. PNRao November 16, 2013 at 12:10 PM - Reply

    Hello Stella, Thanks for your comments and suggestion.
    I am working on real life projects and will be posted in few days.

    Thanks-PNRao!

  7. Sew November 17, 2013 at 2:21 PM - Reply

    Hi I am from South Africa. Thanks for the excellent tutorials…. I have been using EXCEL since it first started and still have problems with VBA… This site gives us a better understanding than the books by —…. Keep up the excellent work… and I am not afraid to say that we are going to squeeze out every bit of your knowledge….. To all the visitors of this site, MARK this site and learn… Knowledge is power and every day we learn… no matter how old we get. Thank you from South Africa- Sew!

  8. PNRao November 17, 2013 at 10:03 PM - Reply

    Thanks a lot, you are most welcome to our blog! I try my level best to share my knowledge in Excel VBA and Data analysis.

    Regards-PNRao!

  9. puppy December 2, 2013 at 11:58 AM - Reply

    hi pnrao i nedd to copy data one by one from sheet 1 to sheet 2 using loop can u please explain me

  10. PNRao December 2, 2013 at 11:02 PM - Reply

    You can use for loop to do this. If you want to copy the data from Cell A1 to A10

    For iCntr=1 to 10
    Sheet2.Cells(iCntr,1)=Sheet1.Cells(iCntr,1)
    Next iCntr

    Hope thus helps!
    Thanks-PNRao!

  11. Suresh December 22, 2013 at 11:43 AM - Reply

    Hi I am very much impressed about your website. And I want to learn VBA for beginner, But when I click on the link which provided it is directing to other things Ex: Learn to record HOW TO link

  12. PNRao December 22, 2013 at 11:46 AM - Reply

    Hi Suresh,

    Thanks for your intrest, here is the link for learning basics of VBA:

    http://analysistabs.com/excel-vba/excel-macros-and-vba-for-beginners/

    Also check our Other Tutorials for VBA:

    http://analysistabs.com/tutorials/

  13. Ankit Bhakuni December 24, 2013 at 5:18 PM - Reply

    I just want to know how could i connect excel with access. I mean i just want to save the data whatever i entered in excel into access table and manipulate data from excel in access i.e. retrieving data from access, updating data in access etc.

  14. PNRao December 25, 2013 at 11:13 PM - Reply

    Hi Ankit,
    You can use ADO and SQL to your tasks, please check the following tutorials on Excel, VBA and SQL.

    http://analysistabs.com/excel-vba/ado-sql-macros-connecting-database/

    http://analysistabs.com/excel-vba/interact-with-other-applications/

    Thanks-PNRao!

  15. Manish Shah December 27, 2013 at 10:03 PM - Reply

    Hello Sir, Thanks for this wonderful blog, I am excel user since last two years and now want to learn VBA. I found your blog from Google search and want to say I got a gem. Thanks, Manish

  16. Mohan December 29, 2013 at 7:58 AM - Reply

    Am getting hooked to Excel, VBA even though I don’t need to do.

  17. PNRao December 29, 2013 at 10:57 PM - Reply

    Hi Manish and Mohan,

    Thanks for your comments. Enjoy learning VBA, you will find lot more free time for enjoying the life.

    Best regards-PNRao!

  18. CHANDRA SHEKHAR JHA January 17, 2014 at 5:07 PM - Reply

    Thanks a lot sir for your Tutorial. It’s awesome. I have seen lot’s of Tutorial. But your way of teaching on net it’s marvelous.Sir Tell me one thing How Can I copy merged cell data in different sheet or different cell. Suppose that A1 to D8 merged data. I have to copy this data in different sheet or different cell. How can I?

  19. PNRao January 17, 2014 at 7:55 PM - Reply

    Hi Chandra,
    Thanks for your comments.
    When you merge Range A1:D8, Range A1 contains all this merged data. So, you can copy the Range A1 values into any other range or sheets.
    Example Shee2.Range(“F5”).value=Range(“A1”).value.

    If you want to, all cells of the merged range to be copied, you can use Range(“A1:D8”).Copy Destination:=Sheet2.Range(“F5”). This will paste from Range F5.

    Hope this helps.

    Thanks-PNRao.

  20. Sebastian February 5, 2014 at 11:23 PM - Reply

    This is of real help for a beginner like me. Hats Off to you Mr. PN Rao

  21. PNRao February 5, 2014 at 11:30 PM - Reply

    Thanks for your comments Sebastian!

    Thanks-PNRao!

  22. Ganesh K February 25, 2014 at 3:18 PM - Reply

    Very good progress of this website. Good jobs “analysistabs” team members for upgrading website. Not only me many of them are Loyal Customer to this website.

  23. PNRao February 25, 2014 at 11:14 PM - Reply

    Thank you Ganesh!
    Regards-PNRao!

  24. chrisham April 15, 2014 at 11:18 AM - Reply

    You have got a great website with some very useful tutorials. I have immediately signed up to your blog. Just installed your add-in and its very unique I must say. A couple of suggestions:
    1) For the List All Files and Folders, you have a very helpful procedure there, what could even be better is a provision for hyperlinks.
    2) For the same List All Files And Folders, I would have preferred a seperate column for the the Folder Name and to be repeated throught out the column. In that way if there is a needs to sort by Folder, it would be easy. Currently the Folder and Files appear in the same column and the file name appears idented.
    3) As someone who benefits from such tutorials, it would help a lot to see the source code. There’s definitely a ton that I could learn on your Add-in developed. Thanks PNRao!

  25. PNRao April 15, 2014 at 11:55 PM - Reply

    Hi Chrisham,
    Thanks for your feedback. I will definitely consider these features in the next release of Analysistab excel add-in.

    Thanks
    PNRao!

  26. Ravinder Yadav April 24, 2014 at 6:46 AM - Reply

    i wan learnt vba macro please send the your contact no my contact no ——–97

  27. PNRao April 24, 2014 at 8:42 PM - Reply

    Hi Ravinder,

    Thanks for your interest to learn VBA with us. Currently we are not providing any training, online training will be available in couple of months.
    We can inform you when we launch our taring programs.

    Thanks-PNRao!

  28. Revathy Panicker May 29, 2014 at 3:32 PM - Reply

    I have a query. I want to fetch data, say from cell A1 to the cell that has the last entry which i’m not sure of because it may increase or decrease according to the data entry of the user. What loop and code do I write for that?

  29. PNRao May 31, 2014 at 1:36 PM - Reply

    Hi Revathy, Welcome to ANALYSISTBS!

    You can find the last valid row in sheet or column as per your requirement.
    Let us say your last row with data is 200
    Then your code will be:

    lastRow=200 ‘ Change this: This is the last row with data in your destination sheet
    Sheets(“Sheet1”).Range(“A” &lastRow+1)=Sheets(“Sheet2”).Range(“A1”)

    Here, Sheet2 is your source and Sheet1 is your destination sheet

    Please refer the below procedures to find last row:
    http://analysistabs.com/excel-vba/find-used-range-last-column-row/
    http://analysistabs.com/excel-vba/finding-last-used-row-with-data-worksheet/

    Hope this helps-Thanks-PNRao!

  30. Ismael Shameem Etowar June 6, 2014 at 2:42 PM - Reply

    Dear AnalysisTabs Team,

    Thank you so much for this website.
    Really an amazing and interesting place to be for newbies like me.

    Keep up with the wonderful job.

    Cheers

    Shameem

  31. PNRao June 8, 2014 at 11:20 PM - Reply

    Hi Ismael,

    Thanks for your feedback, we are happy that you found our site useful.

    Thanks-PNRao!

  32. RAKESH June 26, 2014 at 6:17 PM - Reply

    I know base sas, but to do predective modelling we require to gave statistics knowledge, please can you suggest me some links where i can learn regression,t test,time series etc

  33. PNRao June 26, 2014 at 9:32 PM - Reply

    Hi Rakesh, You can refer these tutorials:
    http://www.ats.ucla.edu/stat/
    You can also search in official SAS website for examples SAS Procs.
    http://www.sas.com/en_us/home.html

    Thanks-PNRao!

  34. Muraleedharan Krishnan June 27, 2014 at 3:01 PM - Reply

    I have an excel file contains 3 sheets two of them hidden.
    I wrote a VBA code to toggle between sheets by hyperlink.
    I wish to have a code to hide automatically the visible sheet when i click the hyperlink to the hidden sheet.
    I mean at any point of time only one sheet should be visible.
    How to acheive this by a VBA code.
    pl help.

  35. PNRao June 27, 2014 at 11:28 PM - Reply

    Hi Muraleedharan,

    Using hyperlinks to set the visibility of sheets is not recommended, it will go to infinite loop some times.

    Instead- you can insert three shapes and call this macro for each button:

    You can use the follow the below procedure to make only one particular sheet:

    Open VBA Editor and Insert new module and add the below code.
    And change your sheet names accrdingly, in place of “Name of Your 1st Sheet”, “Name of Your 2nd Sheet”, “Name of Your 3rd Sheet”

    Sub sbShowSheet1()
    Call sbShowOnlyOneSheet("Name of Your 1st Sheet")
    End Sub

    Sub sbShowSheet2()
    Call sbShowOnlyOneSheet("Name of Your 2nd Sheet")
    End Sub

    Sub sbShowSheet3()
    Call sbShowOnlyOneSheet("Name of Your 3rd Sheet")
    End Sub



    Sub sbShowOnlyOneSheet(ByVal sheetName As String)
    Dim sht
    For Each sht In ThisWorkbook.Sheets
    If sht.Name = sheetName Then
    sht.Visible = True
    Else
    sht.Visible = False
    End If
    Next
    End Sub

    Now insert 3 shapes on your worksheet from Insert Menu:

    Right click on shape 1 and Assign Macro, Select ‘sbShowSheet1’
    Right click on shape 2 and Assign Macro, Select ‘sbShowSheet2’
    Right click on shape 3 and Assign Macro, Select ‘sbShowSheet3’

    Hope this helps! Thanks-PNRao!

  36. Chandra Shekhar Jha June 30, 2014 at 3:47 PM - Reply

    What would be the output
    Private Sub UserForm_Initialize()
    Dim xlrange As Excel.Range
    Dim sectionName As String
    Dim rowCount As Long
    Dim currentRow As Long

    Me.Caption = gsPROGRAMNAME

    ‘If gwkbNewFile Is Nothing Then Set gwkbNewFile = Workbooks(“Coca-ColaAmatilLimited_PapuaNewGuinea_S.xlsm”)

    With gwkbNewFile.Worksheets(gsQRAListing)
    rowCount = WorksheetFunction.CountA(.Range(“A:A”))

    ReDim values(rowCount – 1, 1)

    ‘I’ve limited the number of rows that are checked for data to 3 times the number of
    ‘filled rows, this should easily cover any spaces in the row data
    For Each xlrange In .Range(“A1:A” & (rowCount * 3))
    If xlrange ” Then
    values(currentRow, 0) = .Cells(currentRow + 1, 1)

    sectionName = .Cells(xlrange.Row, xlrange.Column + 1)
    If .Cells(xlrange.Row, xlrange.Column + 2) ” Then _
    sectionName = sectionName & ” > ” & .Cells(xlrange.Row, xlrange.Column + 2)
    sectionName = sectionName & ” > ” & .Cells(xlrange.Row, xlrange.Column + 3)

    values(currentRow, 1) = sectionName
    Else
    Exit For
    End If

    currentRow = currentRow + 1
    Next xlrange
    End With

    ‘Get the Before/After Columns for the review.
    With gwkbNewFile.Worksheets(gsQRATable)
    Set xlrange = getLastCell(aColumn, gwkbNewFile.Worksheets(gsQRATable))

    lastBefore = xlrange.Column
    lastAfter = lastBefore + 1
    End With

    cboSection.List = values
    End Sub

  37. Muraleedharan Krishnan June 30, 2014 at 6:24 PM - Reply

    Thank you for your reply. Yes the code you have given is helpful.
    I am sharing the link of my file in dropbox.
    https://dl.dropboxusercontent.com/u/48823189/Project%20.xlsm
    This is a sample project. I want to hide sheet1 automatically when i am in sheet 2. i.e. only one sheet should be visible all time. Please suggest a VB Code.
    Thanks and Regards
    Muralee

  38. PNRao July 1, 2014 at 1:17 AM - Reply

    You are almost done!
    Paste the same code in all three sheet modules:

    Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    linkto = Target.SubAddress
    wherebang = InStr(1, linkto, "!")
    If wherebang > 0 Then
    mysheet = Left(linkto, wherebang - 1)
    Worksheets(mysheet).Visible = True
    For Each sht In ThisWorkbook.Sheets
    If sht.Name <> mysheet Then sht.Visible = False 'hide except the required sheet
    Next
    End If
    End Sub

    Thanks-PNRao!

  39. PNRao July 1, 2014 at 1:41 AM - Reply

    Hi Chandra,
    ANALYSISTABS comments are prefixed with AT:


    'AT: This code is calling while initializing the form: when run the for this code will run
    'AT: This code is to read the data from sheet gsQRAListing (sheet name declared in your code) and find the list of values to be populated in the cboSection (cobmo box in the form)
    '
    Private Sub UserForm_Initialize()

    'AT: Declaring the variables to store the values temporarily

    Dim xlrange As Excel.Range
    Dim sectionName As String
    Dim rowCount As Long
    Dim currentRow As Long

    'AT: This will change the form caption/title as the value assined to gsPROGRAMNAME
    Me.Caption = gsPROGRAMNAME

    'If gwkbNewFile Is Nothing Then Set gwkbNewFile = Workbooks("Coca-ColaAmatilLimited_PapuaNewGuinea_S.xlsm")

    With gwkbNewFile.Worksheets(gsQRAListing)
    'AT:Rows with Data in Column A
    rowCount = WorksheetFunction.CountA(.Range("A:A"))

    'AT: Resize the Arry eqals to RowCount found in the above line
    ReDim Values(rowCount - 1, 1)

    'Looping each row in and getting the values from Column 3 based on criteria*
    'I’ve limited the number of rows that are checked for data to 3 times the number of
    'filled rows, this should easily cover any spaces in the row data
    For Each xlrange In .Range("A1:A" & (rowCount * 3))
    If xlrange = " Then
    Values(currentRow, 0) = .Cells(currentRow + 1, 1) 'AT:criteria
    sectionName = .Cells(xlrange.Row, xlrange.Column + 1)
    If .Cells(xlrange.Row, xlrange.Column + 2) = " Then _
    sectionName = sectionName & " > " & .Cells(xlrange.Row, xlrange.Column + 2)
    sectionName = sectionName & " > " & .Cells(xlrange.Row, xlrange.Column + 3)

    Values(currentRow, 1) = sectionName 'AT:This is the arry it is creating to fill in the Cobo Box
    Else
    Exit For
    End If

    currentRow = currentRow + 1
    Next xlrange
    End With

    'Get the Before/After Columns for the review.
    With gwkbNewFile.Worksheets(gsQRATable)
    Set xlrange = getLastCell(aColumn, gwkbNewFile.Worksheets(gsQRATable))

    lastBefore = xlrange.Column
    lastAfter = lastBefore + 1
    End With

    cboSection.List = Values ' AT: Finally filling the combo box with the arry 'Values'
    End Sub

  40. Muraleedharan Krishnan July 1, 2014 at 10:25 AM - Reply

    Thanks a lot Mr. Rao. The code is perfect. Thank you for the support.

  41. Muraleedharan Krishnan July 2, 2014 at 10:32 AM - Reply

    Mr. Rao I wish to join for Excel VBA online Training classes. I am a beginner in VBA don’t have any programming background. Can I follow your classes and get benefit out of it.
    Regards
    Muralee

  42. PNRao July 2, 2014 at 3:44 PM - Reply

    Hi Muralee,

    Thanks for your interest to learn VBA with us. Yes, we are re-working on our training program to help every one to make sure both Beginners & Advanced users / Programmers & non-Programmers are benefiting from our online class modules. We are going to launch the Demo class room soon, you can visit our online class room before enrolling for our complete program. Then you can decide to join if you find the demo class is useful and understandable.

    Thanks-PNRao!

  43. Kishor July 4, 2014 at 12:08 PM - Reply

    I have one worksheet with 50 email address I want to send 50mails with worksheet data in email body not attachment.

  44. PNRao July 4, 2014 at 4:19 PM - Reply

    Hi Kishore,

    You can use ActiveSheet.MailEnvelope approach to easily send a range from a specific worksheet:


    Sub Send_Excel_Range()

    'Select a Range
    ActiveSheet.Range("A1:D8").Select

    'Start an Envelope
    ActiveWorkbook.EnvelopeVisible = True
    EmailToSend="emiladdress1.com;emiladdress1.com;...All your 50 addresses separated by semicolon"

    'Set required options and send
    With ActiveSheet.MailEnvelope
    .Item.To = EmailToSend
    .Item.Subject = "Required Subject"
    .Item.Send
    End With
    End Sub

    Hope this helps!
    Thanks-PNRao!

  45. Kishor July 4, 2014 at 10:26 PM - Reply

    Thnks so much sir and that is very much important.
    Ex.
    But sir i have one sheet like “team performance” ok sheet1 is my performance detail and ‘sheet2 is users email id coloum my subject coloum
    In coloum “d”,cc colum,signature’ pls help on this

  46. Kishor July 5, 2014 at 11:08 AM - Reply

    Thnks for this!
    Sir,
    I have workbook that have 2sheet i give ex.
    “Sheet1 is my email body & sheet2 is my mail id “coloumA1, for users name,B1:- users email id and D1:-subject then cc:,bcc,To is my B1.because every day i short listed few users so i can use only few uaers id and send mail with worksheet , i cant piassibal every time make changes in vb.

  47. Kishor July 5, 2014 at 12:35 PM - Reply

    Today i reg. My email.id pls provide me procedur how to join your class.

  48. PNRao July 5, 2014 at 5:56 PM - Reply

    Hope this helps…

    Sub Send_Excel_Range()

    'Select a Range
    Sheets("Sheet1").Activate
    Sheets("Sheet1").Range("A1:D8").Select

    'Start an Envelope
    ActiveWorkbook.EnvelopeVisible = True

    'To:
    EmailToSend = Sheets("Sheet2").Range("B1")
    'if your email ids are in B1,B2,B3... like this, you have to use a for loop to form the above varible
    For iCntr = 2 To 20 ' last row with email id
    If Trim(Sheets("Sheet2").Range("B" & iCntr)) <> " Then EmailToSend = EmailToSend & ";" & Sheets("Sheet2").Range("B" & iCntr)
    Next

    'CC:
    EmailCCSend = "Your CC ids" ' or you can do it same like To, shown above

    'BCC:
    EmailBCCSend = "Your BCC ids" ' or you can do it same like To, shown above

    'Subject:
    EmailSubject = Sheets("Sheet2").Range("D1")

    'Set required options and send
    With ActiveSheet.MailEnvelope
    .Item.To = EmailToSend
    .Item.cc = EmailCCSend
    .Item.bcc = EmailBCCSend
    .Item.Subject = EmailSubject
    .Item.Send
    End With
    End Sub

  49. PNRao July 5, 2014 at 6:00 PM - Reply

    Hi Kishor,

    Thanks for showing your interest to learn VBA @ANALYSISTABS. We are re-working on the program and you can able to join on 1st August 2014. We will send an email notification with the discount coupon and you can use it while joining our classes.

    Thanks-PNRao!

  50. kishor July 5, 2014 at 6:53 PM - Reply

    thanks sir,

    same coding i can use with outlook supperot …….is it right .

  51. PNRao July 6, 2014 at 3:42 PM - Reply

    Yes, it works well if your email application is Outlook!

    Thanks-PNRao!

  52. Roy July 9, 2014 at 9:08 PM - Reply

    Can the tab on the excel quick access bar be changed from “ANALYSISTABS to “Analysistabs” (i.e from Upper to Proper

  53. PNRao July 11, 2014 at 1:06 AM - Reply

    Hi Roy,
    Sure! Thanks for the Suggestion. I w’ll implement this while releasing the next version (August 2014).

    Thanks-PNRao!

  54. Muraleedharan Krishnan July 11, 2014 at 3:23 PM - Reply

    Dear Mr. Rao,
    Pl refer to my query dated 30th June 2014 and your reply dated 1st July 2014. The code you have suggested is working fine if we didn’t change the sheet name. If we change the sheet name I am getting Run time error 9 Type mismatch!
    How to solve? Please help.
    Regards
    Muralee

  55. PNRao July 11, 2014 at 5:42 PM - Reply

    Hi Muralee,

    I remembered that we have placed the below code in every sheet module:


    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$A$1" Then
    Call sbShowOnlyOneSheet("Sheet1")
    ElseIf Target.Address = "$A$2" Then
    Call sbShowOnlyOneSheet("Sheet3")
    ElseIf Target.Address = "$A$3" Then
    Call sbShowOnlyOneSheet("Sheet3")
    End If
    End Sub

    And you need to change the Sheet1, Sheet2, Sheet3; in this code. You can provide me the file, so that I can edit the macro and send it back to you.
    Please note: If your file is holds the real-time data, please delete it before sharing it online and provide the file, even empty file works.

    Thanks
    PNRao!

  56. Muraleedharan Krishnan July 12, 2014 at 11:05 AM - Reply

    Dear Mr. Rao,

    Thank you for your support. Pl find the link below.
    https://dl.dropboxusercontent.com/u/48823189/Project.xlsm
    Please help me to sort out the issue.

    Regards
    Muralee

  57. PNRao July 12, 2014 at 12:55 PM - Reply

    Hi Muralee,

    Here is the updated file.
    Project

    I have done the small change to the existing code:

    From: mysheet = Left(linkto, wherebang – 1) ‘————> This code will create issue when you have sheet name with more than word – your hyperlink bind the sheet name within single quotes “INCOME STATEMENT” will become ” ‘INCOME STATEMENT’ ” in your hyperlink address.

    To: mysheet = Replace(Left(linkto, wherebang – 1), “‘”, “) ‘Removed the single quotes, so that it will be same as your worksheet name.

    And the other change in the individual sheets:
    It should be Worksheets(“INDEX”).Select , instead of Worksheets(“sheet1”).Select.

    Here is the updated file:

    Project.xlsm

    Hope this helps! Thanks-PNRao!

  58. Muraleedharan Krishnan July 12, 2014 at 1:41 PM - Reply

    Dear Mr. Rao,
    Excellent. Thank You very much for the code.
    Regards
    Muralee

  59. PNRao July 12, 2014 at 6:57 PM - Reply

    You are welcome!

  60. Chandra Shekhar July 23, 2014 at 2:39 PM - Reply

    I have a two different worksheet with the name of GXA319 or another is GXA3120 with same content.
    With same column name like first worksheet name GXA319
    Block Byte Bits Value BroadcastSheetCoordinates
    AA 77 88 XKS Chandra shekhar
    In another work sheet the same thing is there name GXA3120
    Block Byte Bits Value BroadcastSheetCoordinates
    AA 77 88 XKS Chandra shekhar
    Fist of call by selected by some browse button and there is an option to choose the particular sheet from both XMLS. The compared output is copied to new sheet which highlight the changes in some color I need a macro code for this.. please help me in this matter

  61. PNRao July 23, 2014 at 10:29 PM - Reply

    Hi Chandra,

    Please send me the example files and detailed requirement to info@analysistabs.com.

    Thanks-PNRao!

  62. neha July 25, 2014 at 3:34 PM - Reply

    I have 50 users i want saving workbook auto because every time i told to my team is not possible so pls provide me 50 users auto saving workbook vba coding (every 10min auto save)

  63. PNRao July 26, 2014 at 12:27 AM - Reply

    Hi Neha,
    Here is the example Macro to Save the workbook every 10 minutes:
    SaveWorkbookEvery10minutes.xlsm

    Or – You can follow the below steps:

    > Insert Worksheet and Name it as “AutoSave”
    > Enter your user IDs in the Column A (Make sure, this user names should match with the Excel Application User Name)
    > And Open VBA and Paste the following code in the Workbook module:

    'Every time user change the data, I will check the time and save the file if it is >=10 minutes
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If ActiveSheet.Name <> "AutoSave" Then
    sbAutosave
    End If
    End Sub

    'Every time user change the seletion, I will check the time and save the file if it is >=10 minutes
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If ActiveSheet.Name <> "AutoSave" Then
    sbAutosave
    End If
    End Sub

    'This is the macro to Save the file automatically
    Sub sbAutosave()

    userIndex = 0
    nMins = 0
    On Error Resume Next

    userIndex = Application.WorksheetFunction.Match(Application.UserName, Sheets("AutoSave").Range("A1:A51"), 0)

    If userIndex > 0 Then
    nMins = Minute(TimeValue(Now()) - TimeValue(Sheets("AutoSave").Cells(userIndex, 2)))
    If nMins >= 10 Or Sheets("AutoSave").Cells(userIndex, 2) = " Then
    Application.EnableEvents = False
    Sheets("AutoSave").Cells(userIndex, 2) = Now()
    Application.EnableEvents = True

    ThisWorkbook.Save
    End If
    End If

    End Sub

    Hope this helps!
    Thanks-PNRao!

  64. Elango July 31, 2014 at 6:23 PM - Reply

    This blog is really awesome!!!

  65. Elango July 31, 2014 at 6:48 PM - Reply

    I need an help to create a dynamic macro, which will copy a particular text from a web page and paste it in excel, could you please assist me on this. I don’t have any idea how to do it? though i’ve browsed for more than two weeks to get any glimpse, but i’ve not found it till now.

    I really appericate your help on this!!!

  66. neha August 19, 2014 at 5:40 AM - Reply

    I have 30 name in a1 cell and also 50 users excel sheet on the path i want those only 30 workbook compile in the one sheet…

  67. trichloroacetic acid warts August 20, 2014 at 4:31 AM - Reply

    Suggestions how have to be get moving on it. Before heading to sleep
    at nite, you have submit an application utilizing with a 100 % organic cotton wool
    right after apply doing it on your affected regions.
    Afterward performing this, you will have to manage these people bandage leaving it
    also in relation to magically. You might sense small puffiness although that is certainly general and shall go down potentially.

  68. Doug jones August 21, 2014 at 8:37 PM - Reply

    How do do you add data to the Create New Dasboard panel.

  69. Ken August 26, 2014 at 6:19 PM - Reply

    It appears that the Analysistabs Excel add-in is not working. I have disabled all popup blockers, tried IE, Chrome and Firefox – none will download it. The demo downloads fine. Can you point me to where I can download?
    Thanks!

  70. PNRao August 26, 2014 at 6:53 PM - Reply

    Hi Ken,

    It is working fine, please press F5 and try downloading again.

    Thanks-PNRao!

  71. Ken August 28, 2014 at 2:23 AM - Reply

    Thanks PNRao,
    But it doesn’t download. Did you test within your firewall? I have also tried 2 different computers, an Android mobile device and an iPad. None could download the file.
    Halp! Thanks in advance.
    ken

  72. Ken August 28, 2014 at 2:37 AM - Reply

    PNRao,
    All set. I viewed your HTML source, found the link and downloaded from there.
    Best,
    Ken

  73. CHANDRA JHA September 3, 2014 at 4:50 PM - Reply

    Hi,
    Rao sir,
    I have a one doubt could you sort out this plz.
    I have to create a Dashboard summary(or report generation summary) where
    If i enter start date & End,Milestone date manually then whoever is working
    between this date(engineer wise those who are working to display not those who left.
    In this dashboard display the engineers working on the specified project.
    Activities respect to the standard duration will be tracked based on the resource and effort.
    so once I click the Button then it will generate the reports.

  74. Carlos Burrell September 11, 2014 at 9:22 PM - Reply

    Compile error in hidden module: modUpdata
    Unable to run ANALYSTABS cannot rub the macro”asbPnraoCreateDashboard”.

  75. Krishnaprasad September 20, 2014 at 4:45 PM - Reply

    Hi, I have an excel sheet contains data and have 23 different tabs. I need to send the emails with the click of buttons and need to attached the tab as excelsheet. Need to send the different tabs to different email addresess. Please let me know how i can do this.

    Also, I have the data looks for a month in each tab, filling the data automatically as of now. But i need to unhide the rows which is not filled after today. Please let me know how i can do this?

    Regards,
    Krishnaprasad PB

  76. R VIJAYAKUMAR September 29, 2014 at 5:04 PM - Reply

    I have a problem with Worksheet Change event in Excel. The following is a simple worksheet containing the bare details

    col D Col F col G col H col I ….
    Composition of X Y Z
    Materials %
    Material X 100 0 0 0 0 0 0
    Y 0 100 0 0 0 0 0
    Z 0 0 100 0 0 0 0
    XY 30 70 0 0 0 0 0
    YZ 0 50 50 0 0 0 0
    XZ 40 0 60 0 0 0 0
    Like this, it goes more than 3000 rows. I am selecting Material composition (col D) through a drop down list. I am able to put values for materials X,Y and Z if they are single and 100%. But I face problems when it comes to combined materials namely XY,YZ,XY etc. I can give any one value for these and the other share must be calculated. Can you suggest a procedure for this?
    Values in col D are subject to change every now and then, accordingly the values in other columns must change. Await your suggestion please.

  77. Roman October 3, 2014 at 12:41 AM - Reply

    Hi,

    I really like your section on filtering a table but I need a more complex code. I want to filter a field of my table with a value I get in another part of the spreadsheet (it’s a date field).

    Is this possible ?

    I use this code for a pivotTable :

    .PivotTables(1).PivotCache.Refresh
    With .PivotTables(1).PivotFields(“myField”)
    .ClearLabelFilters
    .PivotFilters.Add _
    Type:=xlDateBetween, Value1:=myDate1, Value2:=myDate2

  78. fakru October 3, 2014 at 3:15 PM - Reply

    would like to get help from you for customizing excel charts based on our company corporate brand. whats the best way to do it is it styles or having different addin for branding

  79. PNRao October 3, 2014 at 8:49 PM - Reply

    Hi Fakru,
    Its good have an Add-In. So that any one can use the tools and apply the chart formats as per your Organization standards.

    Thanks-PNRao!

  80. Barbara October 5, 2014 at 1:39 AM - Reply

    Hello,

    Thank you for your Free Excel add in. Unfortunately, keep getting error messages. I have 2010.
    http://screencast.com/t/aTIoVQDNv
    http://screencast.com/t/SmfrkFrR
    http://screencast.com/t/KlkXWRBqP
    http://screencast.com/t/F0eBwmrrONt

    Any ideas / help? Thank you.

  81. chander shekhar mahendru October 13, 2014 at 9:16 PM - Reply

    I have workbook having 5 worksheet.out of 5 worksheet 4 worksheets are hidden and only one is visible. I want to print these 1 hidden worksheets with their independent command button without making them visible in vba excel2010.is this possibel

  82. Manuel Slawkin October 14, 2014 at 10:16 PM - Reply

    I am an 81 year old non-programmer who taught himself the basics of excel and loves to use it … in the past year, I took an interest in Sudoku puzzles and have attempted to develop my own “model” to assist in solving them … I take much pleasure in doing it, and am continually enhancing the workbook … however, when I look at Sudoku forums on line, almost all have “solvers” that are made by programmers and their models just process quickly and insert answers into the puzzle … mine is VERY GRAPHIC AND COLORFUL and requires the user [me] to look at “hints” and then enter data…

    Anyhow, in one of my “versions” I wanted to create a MACRO to be able to change FILL color and FONT color, type and size … that was way above my excel capabilities and I located someone who took my INCORRECT macro and emailed me a VBA [something?] and told me to insert it in my workbook’s macro … it worked GREAT!

    Then, recently, I revised the basic structure by adding columns with other formula to develop hints … well, since “my” macro was based on moving across columns and searching for blanks, the ADDITIONAL columns have messed it up …

    I looked at the VBA code in “my” macro, but I don’t understand VBA [never learned it and maybe I’m too old to do it now . ] … but I do see parts of the VBA that relate to columns [ie…. “cOffset = 6 To 16”] so I just counted the position of the new columns and changed the code to read cOffset = 7 To 19, etc — I revised all references to columns and then tried the Macro … needless to say, it FAILED!

    I realize this is probably too simple a problem for you and your forum readers but if you “might” be interested I would gladly email you a word doc I’ve made that contains screen prints of the OLD model and the NEW model [not the actual excel files, unless you might want them] and a copy of the MACRO that was first used …

    If you read this, thank you for your patience with this old man …

    Sincerely,

    Manuel {Manny] Slawkin

  83. PNRao October 16, 2014 at 1:45 PM - Reply

    Hi Sir,

    Thanks for visiting us. It’s looking like you are doing something very interesting. I am happy to help you, you can send me the word document/excel + current macro to info@analysistabs.com.

    Thanks-PNRao!

  84. Manuel Slawkin October 18, 2014 at 11:45 PM - Reply

    Today at 10:13 AM
    Dear Mr. [?] Rao …
    I’m so happy that you ACTUALLY did respond to my inquiry …
    I am attaching the word doc file I mentioned, and ALSO my most recent Excel version of my “model” [if that would be of interest/use to you] …
    If you would like to see my process steps for solving SUDOKU, I could also send you my attempt at “self-instructions” …
    Thank you, again, for your attention to my request …
    Sincerely,
    Manuel [Manny] Slawkin

  85. Rony October 22, 2014 at 1:29 PM - Reply

    Hello,
    Could you please help me with the ADODB VBA SQL code for inserting data from excel to access database.

    Thanks
    Rony

  86. kishor October 24, 2014 at 6:45 AM - Reply

    Hi sir,
    Can you help me for auto macro run on specific time when my workbooks are closed.
    Becuase all events are found after sheet opning so can i use outlook vba for opning workbook because my outlook is any time open when i am on leave. So pls suggest as your thinking.

  87. PNRao October 24, 2014 at 7:42 PM - Reply

    hi Kishor,
    You can create an Excel Macro and VBScript to run the macro. Then you can schedule the VBScript as per your requirement.
    To open the Task Scheduler in your windows computer:
    Stat -> All Programs -> Accessories -> System Tools

    hope this helps!
    Thanks
    PNRao!

  88. Nichlas October 25, 2014 at 3:39 PM - Reply

    how to remove analysistabs from excel?

  89. PNRao October 25, 2014 at 9:26 PM - Reply

    Hi Nichlas,
    Go to DeveloperTab in the main ribbon.
    Then click on the Add-ins command
    Then un-check the analysistabs add-in from the list.

    Thanks
    PNRao!

  90. Kai Hansen November 17, 2014 at 11:14 PM - Reply

    Hi Sir
    Somebody programmed an Excel-tool for me with Makros.
    Some list are assumed in dropdown fields, text-fields and multiple selection-fields.

    Now the problem is: It runs on windows, but not on Macs. Too many users unfortunally work on Apple Macs.
    I am not able to solve the problem. It ist just 1 mask. All the data are in one excel. Its all virgin.

    Can I send the Excel-file to a supporter? Can somebody just change the codes on the makro of the 6-7 dropdowns and 15 multiple selection-fields. I will do a donation or pay an amount.
    Thank you for an answer.
    kind regards
    Kai

  91. Manikanta H K November 25, 2014 at 10:54 AM - Reply

    Hi,
    I had written a macro in PERSONAL.xlsb. Whenever I opened a particular excel sheet, the macro should run on that active sheet.
    But the problem is its running in PERSONAL.xlsb rather that the active sheet and Every time I am clicking on run macro button.
    How to overcome this problem. And which event should I use in This Workbook, such that it will run active sheet.

  92. PNRao November 26, 2014 at 12:26 AM - Reply

    Hi Manikanta,

    Make sure you are referring the Activesheet of the Activeworkbooksheet object.
    For example: You can open the VBA editor and place the below code in ThisWorkbook module of the PERSONAL.XLSB, it will print 1 at Range A1 of the active sheet any time you open any excel file:
    Private Sub Workbook_Open()
    ActiveWorkbook.ActiveSheet.Range(“A1”) = 1
    End Sub

    Please note: This code will print 1 in whenever you open any Excel file, please change it accordingly.

    Thanks
    PNRao!

  93. Ravi December 3, 2014 at 5:29 PM - Reply

    I wish to learn excel macro.

  94. PNRao December 4, 2014 at 6:50 PM - Reply

    Hi Ravi, We are working on the training program, and we will notify when the course is available.
    Thanks
    PNRao!

  95. shane ong December 10, 2014 at 7:30 PM - Reply

    Hi Team,

    I have a work related excel problem.

    I am trying to extract data from a spreadsheet that shows who has not submitted time sheets from different departments. I want to show in a different spreadsheet with different tabs(each tab for each department) that those who did not submit time sheets are denoted as value “1”. Those who have are denoted as “0”.

    Both spreadsheets are not arranged in any order.

    Could you please advise me. (:

    Thank you!
    Warm Regards
    Shane Ong

  96. Earl December 17, 2014 at 5:34 AM - Reply

    Can you point me in the direction I need to look for a way to use VBA to compare two tables, both in separate workbooks, for various conditions. I am starting by just wanting to do a simple vlookup to check if items in table 1 are also in table 2. Both tables are somewhat identical, same type of data one set pulled today the other pulled last week. I am trying to compute the differences and save to a 3rd table in a 3rd file. What I currently am trying to do is just get a simple hard coded VLookup to work with the table names I have, but I am missing something and it errors out.

    The sub I currently have looks as follows; but File names listed in as the top two variables are assigned by a separate sub.

    Public LatestMasterTrackerFileName As Variant
    Public EarlierMasterTrackerFileName As Variant
    Public LatestMasterTrackerWorkbook As Variant
    Public EarlierMasterTrackerWorkbook As Variant
    Public TempValue As Boolean

    Public MyLatestMasterTrackerTable As ListObject
    Public MyEarlierMasterTrackerTable As ListObject

    Sub CompareEarlierToLatest()
    Workbooks(LatestMasterTrackerWorkbook).Worksheets(“Indoor – All Regions”).Activate
    ActiveSheet.ListObjects(“Table_exts019_ProposalTracker_vwFinancialSummaryIndoor2014”).DataBodyRange.Select
    Set MyLatestMasterTrackerTable = ActiveSheet.ListObjects(“Table_exts019_ProposalTracker_vwFinancialSummaryIndoor2014”)

    Workbooks(EarlierMasterTrackerWorkbook).Worksheets(“Indoor – All Regions”).Activate
    ActiveSheet.ListObjects(“Table_exts019_ProposalTracker_vwFinancialSummaryIndoor2014”).DataBodyRange.Select
    Set MyEarlierMasterTrackerTable = ActiveSheet.ListObjects(“Table_exts019_ProposalTracker_vwFinancialSummaryIndoor2014”)

    TempValue = Application.WorksheetFunction.VLookup(MyEarlierMasterTrackerTable.DataBodyRange(1, 1).Value, LatestMasterTrackerWorkbook.Worksheets(16).Range(“Table_exts019_ProposalTracker_vwFinancialSummaryIndoor2014”), 2, False)
    MsgBox TempValue

    End Sub

  97. Earl Kubiak December 23, 2014 at 4:09 AM - Reply

    I could use any help you could provide on the simplest VBA program to compare a few columns (one column at a time) between two separate tables located in two separate Excel Files. Both tables would be the same except for possible changes in data over time. I cannot find any information on how to do this and if the best way to go is VLOOKUU or INDEX-MATCH. I tried to post to your site last week; but I don’t think it went through. Any advice would be appreciated.

  98. S P Kumar January 20, 2015 at 6:35 PM - Reply

    Hi

    I need a coding for particular unicode files move from source folder to destination folder.

    Eg : Source folder contains 50 files and in those file few file only having unique code 429147 in column P. So those particular files should move from source folder to destination folder. Please help me.

  99. Roland January 23, 2015 at 10:25 PM - Reply

    Hi, I’ve created several pivot tables in 1 worksheet. How do you make each pivot table not overwrite the other pivot table. Can the pivot table insert rows automatically so that it will not overwrite the other pivot table below?

    Thanks.

  100. PNRao January 25, 2015 at 11:31 PM - Reply

    Hi Roland,
    Pivot Tables can not insert the rows or columns automatically. We have to place them maximum free rows/columns. Then you can use VBA to hide or unhide the rows/columns.

    Thanks-PNRao!

  101. VJS February 6, 2015 at 1:31 AM - Reply

    How can I add rows to the project plan worksheet?

  102. vyshnavy February 18, 2015 at 11:22 AM - Reply

    Hi,

    I have established ADo connection to SQL throughVBA successfully,but while trying to pass teh sql query as input from excel sheet i could see teh errors due to long query.Can you please help on this

  103. Jon February 27, 2015 at 7:31 PM - Reply

    Hi there,
    I’m looking to use VBA code to change the value of a cell to its negative equivalent when a cell in the same row changes to a certain value.
    For example
    (C1) = 1.0 and (E1) = “Cancelled”, when E1, (and the rest of the column) reads “Cancelled”, I would want C1 to automatically change to its negative value to read -1.0
    Could you advise me on how to do this please?

  104. PNRao March 2, 2015 at 6:48 PM - Reply

    Hi vyshnavy, try to increase the query time out and let me know if still facing any issues.

    Thanks-PNRao!

  105. PNRao March 2, 2015 at 6:53 PM - Reply

    Hi VJS,

    Currently, its locked. You can copy the plan to different workbook and change as per your requirement.

    Thanks-PNRao!

  106. PNRao March 2, 2015 at 7:23 PM - Reply

    Hi Jon,

    You can simply multiply with -1:
    Range(“C1”)=Range(“C1”)*-1

    Thanks-PNRao!

  107. waseem March 5, 2015 at 12:00 PM - Reply

    i want to make a micros software for a institute, company,for worker and institute payement .
    please contact me.

  108. Abhishek May 11, 2015 at 6:31 PM - Reply

    Hi,

    I want to learn Macros and came to your website while going through the web. I’m beginner and don’t have any experience about macros. Reading books on macros was not helping since it’s too much of theory and I was not getting good practice – hence no/minimal learning. I liked your website so can you please help me as to where should I start learning about macros on your website (If you can send the link, it will be very helpful).

    Thanks!
    Abhishek .

  109. Muraleedharan Krishnan May 22, 2015 at 2:33 PM - Reply

    Dear Mr. Rao,

    I wish to have a vba code or function in excel to find whole number from a range of whole numbers after a certain %, for a large spreadsheet.

    For e.g. let the range of numbers are 26100 & 27000

    Let X be a whole in the above range and X-X*18% = Y also must be a whole number and Y-Y*5% must be a whole number.

    I know there must be more than one whole number satisfies the above condition.

    How to find those X using a vba code or a function in excel

    Thanks and Regards
    Muralee

  110. Muraleedharan Krishnan May 22, 2015 at 11:42 PM - Reply

    Dear Mr. Rao,

    To clarify a little more.to above query please note the following and help me.

    Let X be a whole number lies between any two whole numbers say 2500 and 2600, where as Y=X-X*n% and Z=Y-Y*m%, X, Y and Z are whole numbers.

    Regards
    Muralee

  111. Muraleedharan Krishnan May 23, 2015 at 10:23 AM - Reply

    Dear Mr. Rao,

    To clarify my query a little better it is like this.

    Let X be a whole number between two numbers say 2500 and 2600, including both. Let Y = X plus or minus X*N% and Z = Z plus or Minus Z*M%. I wish to get a function or a VBA Code to find X which satisfy the above condition.

    Thanks and Regards
    Muralee

  112. Dushyant June 23, 2015 at 10:32 AM - Reply

    Hi P N Rao

    I am looking for a VBA code which take data directly from my excel sheet into command prompt (cmd)
    can you please tell me how to do this please !!

    Regards

    Dushyant Padhya

  113. Gcross35 June 25, 2015 at 1:31 AM - Reply

    I am a intermediate level VBA programmer. However I am having an issue with transfering data from Excel to Access. I need to accomplish it with a button click. Any assistance would be appreciated.

  114. Tom k June 28, 2015 at 8:10 AM - Reply

    Hi,thanks for the informative blog/site. I am trying to figure a WA of inserting a range in spreadsheet into a cell in another, I suppose screenshot is my only option? If so how do I go about renaming the screenshot and how do I insert in another workbook pls.. Would much appreciate your help and advice, also if a table can be minimized into the destination cell??? As an option ( I guess not…)
    Regds
    Tom

  115. Tom k June 28, 2015 at 8:14 AM - Reply

    To clarify the above…
    It’s usedrange of one spreadsheet being captured as screenshot and to B inserted into another Active workbook after renaming the screenshot

  116. Ramya July 29, 2015 at 11:05 PM - Reply

    Hello,

    I want to learn VBA, please let me know when you are going start VBA online classes.

    Thanks-Ramya

  117. Baber Beg August 18, 2015 at 1:43 PM - Reply

    How do I become a member of this site?

  118. PNRao August 19, 2015 at 12:38 AM - Reply

    Hi,
    Thanks for your interest, we are going to open the registration in couple of weeks, you will be informed. You can subscribe to our newsletter to get all the updates.

    Thanks
    PNRao

  119. raushan kumar August 19, 2015 at 1:44 PM - Reply

    sir, we want to in excel , mm/dd/yyyy to dd/mm/yyyy, plz help

  120. PNRao August 20, 2015 at 1:15 AM - Reply

    Hi,

    You can use Excel formula to change the format:

    =Text(A1,”dd/mm/yyyy”)

    You can also use VBA to change the format
    dateFormated= Format(Range(“A1″),”dd/mm/yyyy”)

    Hope this helps!
    Thanks-PNRao!

  121. Hasan August 20, 2015 at 4:01 PM - Reply

    Dear Team,

    does the “Project management Template ” are equally useful for PMs handling small projects in which cost of employees and project cst should not be mentioned, just the teams, tasks assigned and project tracking are to mentioned?

  122. Venkat August 20, 2015 at 6:24 PM - Reply

    Sir,A small query in excel vba, example :- in a procedure i have the following statements ans=msgbox(” Shall I print”,vbyesno ). Now when the msgbox prompts the user for Yes/No and the user presses Esc button the procedure does not exit even if the below statement is added if err.number=18 then exit sub endif and the msgbox still appears on the screen for user input [ yes/no.].I even tried placing the application.enablecancelkey=xlinterrupt. but of no use the msgbox still waits for user input .I don’t want to place a cancel button in the msgbox prompt.Now how to resolve this issue any way out…

  123. PNRao August 21, 2015 at 1:54 AM - Reply

    Hi Hasan,
    These templates for individual and Small-medium teams. And it havs verity options to customize the templates.

    Thanks-PNRao!

  124. Venkat September 2, 2015 at 6:50 PM - Reply

    Sir, What is this oFSO ?? FSO is filesystemobject which I have understood thru Ms.Valli example but could not understand oFSO ? Could u pl clarify …

    Dim oFSO As Object
    Dim oFile As Object

  125. PNRao September 2, 2015 at 11:22 PM - Reply

    Hi Venkat,
    Both FSO or oFSO are user defined names to set an object. You can use any string (which follow the variable naming conventions) to set an object.
    Thanks-PNRao!

  126. Venkat September 4, 2015 at 4:00 PM - Reply

    Dear Sir,

    Thanks for the clarification….

    Regards
    Venkat

  127. Venkat September 4, 2015 at 4:29 PM - Reply

    Dear Sir, sfolder was written thrice by mistake !! please read as below :-

    When using fso.deletefolder sfolder ( sFolder is a variable containing the path and folder name ) the folder is deleted from the defined path.But the folder doesn’t go to recycle bin and is permanently deleted .Now is it possible to route the deleted folder to recycle bin using the same vba code in some other way…

    Regards

    Venkat

  128. James September 9, 2015 at 12:09 AM - Reply

    How do i set password?

  129. PNRao September 9, 2015 at 1:02 AM - Reply

    Hi James,
    I have sent an email, please check your inbox.
    Thanks-PNRao!

  130. Venkat September 9, 2015 at 5:35 PM - Reply

    Hi Mr.P.N Rao !
    Dear Sir, My query is while using for each ws in activeworkbook.worksheets
    ‘for adding hyperlinks in sheet 1 for accessing each of these sheets the subaddress syntax goes like below could u explain the first part ” ‘ ” and the last part ” ‘ ! a1″

    SubAddress:=” ‘ ” & Worksheets(n).Name & ” ‘ ! a1″

  131. Venkat September 14, 2015 at 5:01 PM - Reply

    Venkat September 9, 2015 at 5:35 PM – Reply
    Hi Mr.P.N Rao !
    Dear Sir, My query is while using for each ws in activeworkbook.worksheets
    ‘for adding hyperlinks in sheet 1 for accessing each of these sheets the subaddress syntax goes like below could u explain the first part ” ‘ ” and the last part ” ‘ ! a1″

    SubAddress:=” ‘ ” & Worksheets(n).Name & ” ‘ ! a1″

    Regards
    Venkat

  132. Arjun September 20, 2015 at 7:07 PM - Reply

    Hi,

    I am working on a project in my office where i need to extract data from SQL server. I am not sure how i need to start. I need to create a input box where user can chose the “Run ID”, Table” and fields to extract. SO can you please help me how i can start and how i can make run id ,table ,fields dynamic as they will depend on run id chosen.

    Please help me on this.

  133. Ripan September 25, 2015 at 2:12 PM - Reply

    Hi I have one excel worksheet which contains 25 tables for 25 different clients. One table contains day wise data for transactions over period of 3-4 years for one client. For e.g. Table 1 is From Column A to Column N for Client no.1 & rows contain day wise transactions for 3-4 years, Table 2 from column P to Column AC is for Client no. 2 so on and so forth. Each table has 14 Columns and I want to copy Three important column from each table into another worksheet and while copying I don’t want few cells which are blank (These cells are no transactions days for a client. Each client’s no transaction day may be different).
    In nutshell I want to copy client wise three columns from one worksheet in to separate sheet with ignoring blank cells. I have gone through Macro & array formulae & filter which will copy non blank cell data into another worksheet. Array formulae is making file very slow. But how do I do it for all 25 tables ? Can any one help me for macro in relation to this?

  134. Tegbahadur Singh September 25, 2015 at 3:29 PM - Reply

    Sir/Mam I designed a deshboard like as a ATM password change. I created two userforms first textbox-1 for a/c no and textbox-2 for password when we will click on login then it confirmed by excel sheet and a/c and password. a/c is a unique value in excel and then check password another column, if it will be right then login otherwise sorry you have input wrong details, plz check your a/c no or password. It’s first user form details.
    In second userform have textbox-1 for old password, textbox-2 for new password and textbox-3 for confirm new password. It’s form set by Command Button on the userform-1.
    So while means when i input old password, new password and confirm new password then it will be change in my excel sheet. I created everything but i have not understand which type will be change in excel sheet. So plz guide me.
    Thanks & Regard.

    ———————————
    Tegbahadur Singh
    ———————————-

  135. Tanvir September 30, 2015 at 7:26 PM - Reply

    hi sir,

    how can i get automatic date & time in excel?

    Scenario: in my team we are answering customers queries through facebook, email, sms. how can we calculate the wait time here automatically?

    pls, need your help.

  136. abhishek October 14, 2015 at 5:23 PM - Reply

    Hi, i want a formula or macros based program that can do the following task if possible :
    For ex.

    1 A B C D
    2 32 0-100 10%
    3 43 101-200 20%
    4 151 201-300 30%
    5 218 301-400 40%
    6 384
    7 116
    8 308
    9 351

    Consider ABCD are columns of excel with different values and i want a formula that can return a value in D that will compare each value from Column A one by one and compares with array of B1 to B5, if that value comes in between 0-100 it will return value from C1 in D1, if value comes in between 201-300 then it will return C2 in D2 and so on..

    Can u please help me in this if any formula can do this or any macros function.
    In example i have used a very small table otherwise table is having about 2000 rows

  137. Hyma October 25, 2015 at 5:04 PM - Reply

    Hi – I need your help on the below. I want to copy certain columns from Master Sheet – let’s say A to G and S to Summary Sheet, both of the sheets have the same headings. But the thing is workbook is protected with a password “1234”. I tired with one of the codes i found in your website by adding the following code in the existing summary sheet macro. But it is throwing an error ” Compile error in hidden module: wsSummary.”

    Sub Move_Data_From()
    Sheets(“Master – All Fees”).Columns(“A:G”).Copy Destination:=Sheets(“Foreign Summary”).Range(“C:I”)

    Could you please advise what should be included in the above code for the macro to work. Appreciate your help in this regard.

    Thanks!

  138. vinay November 1, 2015 at 9:48 AM - Reply

    hi Rao…when i am trying to make the payment i cant do it…can you assist me how to do the payment i tried with debit card and credit card but process is incomplete…

  139. vinayaka November 2, 2015 at 1:44 PM - Reply

    Hi Iam finding this site very useful,Thanks a lot for providing all this information.

    I have a doubt,I would very much thankful if u can help me on this.

    I am writing a code , where in the first sheet i will get 5 input values from user and in 2nd sheet i have 20 values(5 will be populated from sheet1,remaining 15 are constant values.. when i execute the code i want the data in sheet2 to be populated into a fixed length flatfile.

    I found a sheet over internet where i can select a row and and populate the file, now i want to modify in such a way that it should automatically select and generate button should be in first sheet.can u please help.

    Regards,
    Vinayak

  140. vinayaka November 2, 2015 at 1:46 PM - Reply

    Hi Iam finding this site very useful,Thanks a lot for providing all this information.

    I have a doubt,I would very much thankful if u can help me on this.

    I am writing a code , where in the first sheet i will get 5 input values from user and in 2nd sheet i have 20 values(5 will be populated from sheet1,remaining 15 are constant values.. when i execute the code i want the data in sheet2 to be populated into a fixed length flatfile.

    I found a sheet over internet where i can select a row and and populate the file, now i want to modify in such a way that it should automatically select and generate button should be in first sheet.can u please help.

    Sub Export_Selection_As_Fixed_Length_File()
    ‘ Dimension all variables.
    Dim DestinationFile, CellValue, Filler_Char_To_Replace_Blanks As String
    Dim FileNum, ColumnCount, RowCount, FieldWidth As Integer
    Dim sht As Worksheet

    ‘Below are options incase you want to change the folder where VBA stores the .txt file
    ‘We use ActiveWorkbook.Path in this example
    ‘ActiveWorkbook.Path ‘the activeworkbook
    ‘ThisWorkbook.Path ‘the workbook with the code
    ‘CurDir ‘the current directory (when you hit File|open)

    ‘If a cell is blank, what character should be used instead
    Filler_Char_To_Replace_Blanks = ” ”

    ‘Check if the user has made any selection at all
    If Selection.Cells.Count < 2 Then
    MsgBox "Nothing selected to export"
    Selection.Activate
    End
    End If

    'This is the destination file name.
    DestinationFile = ActiveWorkbook.Path & "/File_With_Fixed_Length_Fields.txt"
    'Obtain next free file handle number.
    FileNum = FreeFile()

    ' Turn error checking off.
    On Error Resume Next

    ' Attempt to open destination file for output.
    Open DestinationFile For Output As #FileNum

    ' If an error occurs report it and end.
    If Err 0 Then
    MsgBox “Cannot open filename ” & DestinationFile
    Selection.Activate
    End
    End If

    ‘ Turn error checking on.
    On Error GoTo 0

    ‘ Loop for each row in selection.
    For RowCount = 1 To Selection.Rows.Count
    For ColumnCount = 1 To Selection.Columns.Count
    CellValue = Selection.Cells(RowCount, ColumnCount).Text
    If (IsNull(CellValue) Or CellValue = “) Then CellValue = Filler_Char_To_Replace_Blanks
    FieldWidth = Cells(1, ColumnCount).Value
    If (ColumnCount = Selection.Columns.Count) Then
    Print #FileNum, Format$(CellValue, “!” & String(FieldWidth, “@”)) & vbCrLf;
    Else: Print #FileNum, Format$(CellValue, “!” & String(FieldWidth, “@”));
    End If
    Next ColumnCount
    ‘ Start next iteration of RowCount loop.
    Next RowCount
    ‘ Close destination file.
    Close #FileNum
    Selection.Activate
    Workbooks.OpenText Filename:=DestinationFile
    End Sub

    Regards,
    Vinayak

  141. PNRao November 3, 2015 at 4:37 PM - Reply

    Hi Hyma,

    Please find the below macro to move data in protected workbook from one sheet to another sheet.

    Sub Move_Data_From()
        On Error GoTo ErrorOccured
        'Unprotect Workbook
        ActiveWorkbook.Unprotect Password:="1234"
        
        'Move Data from one sheet to another sheet
        Sheets("Master – All Fees").Columns("A:G").Copy Destination:=Sheets("Foreign Summary").Range("C:I")
        
        'Protect Workbook
        ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="1234"
    
    ErrorOccured:
        MsgBox "Workbook could not be UnProtected - Password Incorrect"
        Exit Sub
         
    End Sub
    

    Thanks-PNRao

  142. PNRao November 7, 2015 at 11:27 AM - Reply

    Currently we have issue in accepting the payments from Indian Customers. We will be resolving this as soon as possible.

    Thanks-PNRao!

  143. Nayan Kumar Bhattacharjee November 11, 2015 at 8:08 AM - Reply

    Respected Sir, I have to convert some numeric values generated by excel calculation (say, these are in Column A) into words (say, these should be generated in Column B). I am using the following macro and using =words(A1), I am somehow managing the matter. But I am facing a problem. I need the numeric value should be generated in words with 2 decimal places. When in Column A, say in A1, a numeric value becomes 25.4789, it becomes in Column B1 ‘ Twenty Five Point Four Seven Eight Nine’. But I need ‘ Twenty Five Point Four Seven’, even I don’t want ‘ Twenty Five Point Four Eight’. Another Problem, when in Column A1, a value becomes 0, in Column B1 remains blank without generating Zero. Please edit the macro or provide me proper macro. ——————————————————————————————————————————————————————————————Function words(fig, Optional point = “Point”) As String
    Dim digit(14) As Integer
    alpha = Array(“, “One”, “Two”, “Three”, “Four”, “Five”, “Six”, “Seven”, “Eight”, “Nine”, “Ten”, “Eleven”, “Twelve”, “Thirteen”, “Fourteen”, “Fifteen”, “Sixteen”, “Seventeen”, “Eighteen”, “Nineteen”, “Twenty”, “Thirty”, “Forty”, “Fifty”, “Sixty”, “Seventy”, “Eighty”, “Ninety”)
    figi = Trim(StrReverse(Str(Int(Abs(fig)))))
    For i = 1 To Len(figi)
    digit(i) = Mid(figi, i, 1)
    Next
    For i = 2 To Len(figi) Step 3
    If digit(i) = 1 Then
    digit(i) = digit(i – 1) + 10: digit(i – 1) = 0
    Else: If digit(i) > 1 Then digit(i) = digit(i) + 18
    End If
    Next
    For i = 1 To Len(figi)
    If (i Mod 3) = 0 And digit(i) > 0 Then words = “hundred ” & words
    If (i Mod 3) = 1 And digit(i) + digit(i + 1) + digit(i + 2) > 0 Then _
    words = Choose(i / 3, “thousand “, “million “, “billion “) & words
    words = Trim(alpha(digit(i)) & ” ” & words)
    Next
    If fig Int(fig) Then
    figc = StrReverse(figi)
    If figc = 0 Then figc = ”
    figd = Trim(WorksheetFunction.Substitute(Str(Abs(fig)), figc & “.”, “))
    words = Trim(words & ” ” & point)
    For i = 1 To Len(figd)
    If Val(Mid(figd, i, 1)) > 0 Then
    words = words & ” ” & alpha(Mid(figd, i, 1))
    Else: words = words & ” Zero”
    End If
    Next
    End If
    If fig < 0 Then words = "Negative " & words
    End Function

  144. K. V. N. Rao December 4, 2015 at 2:48 PM - Reply

    Hi,
    I used this code in VBA to copy all the Excel files in a directory to a single worksheet in another workbook.
    I need to copy the excel workbooks in the order they have been created (otherwise the order is getting changed).
    Sub MergeAllWorkBooks()
    Dim SummarySheet As Worksheet
    Dim FolderPath As String
    Dim NRow As Long
    Dim FileName As String
    Dim WorkBk As Workbook
    Dim SourceRange As Range
    Dim DestRange As Range

    ‘ Create a new workbook and set a variable to the first sheet.
    Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)

    ‘ Modify this folder path to point to the files you want to use.
    FolderPath = “D:PERSONALTEST_EXCEL”

    ‘ NRow keeps track of where to insert new rows in the destination workbook.
    NRow = 1

    ‘ Call Dir the first time, pointing it to all Excel files in the folder path.
    FileName = Dir(FolderPath & “*.xl*”)

    ‘ Loop until Dir returns an empty string.
    Do While FileName ”
    ‘ Open a workbook in the folder
    Set WorkBk = Workbooks.Open(FolderPath & FileName)

    ‘ Set the cell in column A to be the file name.
    ‘ SummarySheet.Range(“A” & NRow).Value = FileName

    ‘ Set the source range to be A9 through C9.
    ‘ Modify this range for your workbooks.
    ‘ It can span multiple rows.
    Dim LastRow As Long
    LastRow = WorkBk.Worksheets(1).Cells.Find(What:=”*”, _
    After:=WorkBk.Worksheets(1).Cells.Range(“A1”), _
    SearchDirection:=xlPrevious, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByRows).Row
    Set SourceRange = WorkBk.Worksheets(1).Range(“A1:AM” & LastRow)

    ‘ Set the destination range to start at column B and
    ‘ be the same size as the source range.
    Set DestRange = SummarySheet.Range(“A” & NRow)
    Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
    SourceRange.Columns.Count)

    ‘ Copy over the values from the source to the destination.
    DestRange.Value = SourceRange.Value

    ‘ Increase NRow so that we know where to copy data next.
    NRow = NRow + DestRange.Rows.Count

    ‘ Close the source workbook without saving changes.
    WorkBk.Close savechanges:=False

    ‘ Use Dir to get the next file name.
    FileName = Dir()
    Loop

    ‘ Call AutoFit on the destination sheet so that all
    ‘ data is readable.
    SummarySheet.Columns.AutoFit
    End Sub

  145. Gamini Heraliyawala December 9, 2015 at 11:36 AM - Reply

    Greetings from Sri Lanka

    Dear Rao,

    First of all wish to thank you for sharing your wealth of knowledge with the rest of the world and I pray the Gods blessing will be with you always.

    I just need to ask your advice for me to open and close xlsm files Wk1, Wk2, Wk3, Wk4 & Wk5 defining file the file name variable and open one at a time. When Wk1 is opened and while moving to Wk2 I would like to know the VBA coding on a command control to save and close WK1 and open Wk2 and etc.

    Along with that will I be able to navigate from Userform_X of Wk1 to Userform_Y of Wk2?

    Appreciate your kind advice and sharing the VBA Codes. And Sorry for troubling you on this matter.

    God Bless you and your family,

    Gamini

  146. Marianne January 4, 2016 at 2:59 PM - Reply

    Hello Sir. Thanks so much for the wonderful tips and posts about Excel. I am learning a lot here :)
    However, I need some help as I need to create a macro to insert a blank rows in a sheet based on the data inserted in another sheet. In other words, in case I inserted a new row in sheet2 a new row should be added at the end of the data entered in sheet1. Hope that I am clear enough. Thanks in advance.

  147. MANOHARAN January 8, 2016 at 4:32 PM - Reply

    Dear sir , i am Leaning ADO in excel from your templates only , i write a sql in ADO query , but i did not get my results
    please give solution for that

    Sub CheckVendor()

    Dim CnTDS As New ADODB.Connection
    Dim StCNTDS As String
    Dim DBPath As String
    Dim StRsVendor As String
    Dim RsVendor As New ADODB.Recordset

    DBPath = ThisWorkbook.FullName
    StCNTDS = “Provider=MSDASQL.1;DSN=Excel Files;DBQ=” & DBPath & “;HDR=Yes’;”
    CnTDS.Open StCNTDS

    StRsVendor = “Select [CREDITORS$].[Account Code],[CREDITORS$].[Name]” & _
    “From [CREDITORS$],[MASTER$]” & _
    “Where [CREDITORS$].[Account Code] [MASTER$].[Account Code]” & _
    “Group By [CREDITORS$].[Account Code],[CREDITORS$].[Name];”

    RsVendor.Open StRsVendor, CnTDS

    Sheets(“NotFound”).Range(“A2”).CopyFromRecordset RsVendor

    ‘RsVendor.RecordCount = Nothing

    End Sub

  148. Vial January 15, 2016 at 6:11 PM - Reply

    hi,
    Is there a possibility of copy past from one sheet to another using macros. the data in sheet1 is in vertical way,

    ID Abss SC TCC
    Source Process Dta Process Dta Process Dta Total
    a 10 30 5 45
    b 10 30 5 45
    c 10 30 5 45
    d 10 30 5 45
    e 10 30 5 45
    f 10 30 5 45
    g 10 30 5 45

    Were as the data need to be copy in sheet2 in horizontally way, below is the output.
    a TCC 5
    a SC 30
    a Abss 10
    Total 45
    b TCC 5
    b SC 30
    b Abss 10
    Total 45

    any idea

  149. vishnu February 15, 2016 at 2:12 AM - Reply

    Hello team,
    I am trying to create a macro to format all headings in a ms word document .
    code should automatically find the headings from the content page and format accordingly.
    Moreover if the document contains any tables then it should convert it into a image.
    Can you please help me by giving some ideas.

  150. balwant March 8, 2016 at 2:13 PM - Reply

    Every time it should create New Workbook Then create 1 to 10 tabs in that book copy the respected data on respected tabs in vba program

  151. manoj March 21, 2016 at 3:44 PM - Reply

    give vb code for find particular row contain name,father name , surname from three column in excel 13

  152. Monica March 23, 2016 at 8:20 PM - Reply

    On your Cost Estimator Spreadsheet, can you please tell me the difference between Required Hours and Budget Hours? Thanks.

  153. John Carter May 3, 2016 at 5:58 AM - Reply

    Hi, I downloaded the project mgmt package on my laptop using excel 2010.
    I worked with it and like it. I tried to bring it to my job that uses excel 2007
    and I found the dashboard graphics are blurry and misaligned. Is this a function of the
    different versions or because I copied it from my laptop to my work desktop . Do I need to buy
    another version or pay for an additional copy for work. I need to do this quickly as I need this to
    organize my team.
    Thanks

  154. Bhupender May 22, 2016 at 2:11 AM - Reply

    Hi,

    Sir I have a question. In my office we have E-mail subject line EX- 011 EDH4512 CA. so I want to create user form which allow user to get E-mail from outlook Inbox according to last two character (CA) if check box CA is checked and if is unchecked then user will not get this E-mail

  155. T. S. Rajan July 11, 2016 at 7:17 AM - Reply

    I have an Excel file with first 7 characters of file names in Column C. There are about 1,000 rows. The associated files are stored under multiple sub folders under a main folder. These folders are presently in a flash drive. What would be the best way to open the files using the cell pointer (pointing to different cells in column C) and a macro button to run the code? I am looking for a VBA code to do this. Thanks for helping.

  156. Prasad M July 14, 2016 at 12:21 PM - Reply

    Hi ,

    I am a beginner for VBA, I would like to know how to compare 2 cell Data type values in the XLS .

    My requirement is I have to compare each data type value which is belongs to other column( cell ) in the xls .

    Example :- Let say Column “A” got Cybase Data type text value is ( PIC X(02)) and another column “C” for Oracle Database type text value is ( VARCHAR2(2) ) and I should be populated my result in 3rd column i.e “D” as expected “Matching Database value ” if not it should show as ” Not matching with Data type” . How should we do this ? could you please suggest or give mne the solution for this .

    Thanks in Advance,
    Regards,
    Prasad M

  157. JB October 27, 2016 at 3:07 PM - Reply

    Hi
    Thanks for this wonderful site. I have an issue with a macro solution

    I have many different excel files in a folder(mytest) on my desktop (link2015.xlsx, vlookupTest.xlsm). My intention to do a macro vlookup from one of the file to the other file.I need to use a wildcard to get one of the files (link2015.xlsx) because it will be changing 2ce in a month.

    By clicking the button, it gave an error message: link2015.xlsx could not be found. It surprising because excel got the name right. But couldnt use it. I also tried many things like closing the dir() function and other stuff, but it didnt work. This is just the best output I got.

    Normally by using the full file name directly, it works perfectly well, but using the wildcard operation never worked

    PLS COULD YOU HELP ME OUT WITH THE SOLUTION. I also attach the screenshot of the error

    Private Sub CommandButton1_Click()
    Dim fruits As String
    Dim i As Integer
    Dim wbk As Workbook
    Dim directory As String
    Dim fileName As String

    directory = “C:UsersjomosaDesktopmytest”
    fileName = Dir(directory & “link*.xlsx”)

    Set wbk = Workbooks.Open(fileName)

    For i = 2 To 5
    fruits = Cells(i, 4).Value
    Sal = Application.WorksheetFunction.VLookup(fruits, wbk.Worksheets(“Sheet1”).Range(“A2:B10”), 2, False)
    Cells(i, 5) = Sal

    Next i

    End Sub

  158. vasanth November 18, 2016 at 7:52 PM - Reply

    hi its very useful

  159. Wei Jen December 27, 2016 at 12:00 PM - Reply

    Hi AnalysisTabs

    Good day,

    Thanks for the useful compilation of Msg box codes.

    However, there are some typo/error in the code for VBA MsgBox: vbSystemModal
    Site:http://analysistabs.com/vba/msgbox-examples-macros/

    Regards
    Wei Jen

  160. John April 28, 2023 at 5:18 AM - Reply

    Hi there,

    I am interested in multi project tracking templates n dashboard templates for multi project. Which package is comes with these templates

    • PNRao May 3, 2023 at 8:53 AM - Reply

      You can choose 50+ Excel Templates pack or 120+ All-in-one PM Pack

  161. Constantin May 4, 2023 at 11:12 AM - Reply

    Hello,

    Great excel templates! Congrats to the team. Quick question, If I purchase a template can I use it for work on the project I’m currently working on?

    Thank you.

    Constantin.

    • PNRao June 8, 2023 at 5:55 AM - Reply

      Yes! You can download and use it for your project management!

  162. JV November 23, 2023 at 2:59 AM - Reply

    If i purchase the premium templates, will i be able to remove the brandname of your organization as I intend to use it in my company and for our client but it doesnt allow to use branding of third party on our reports.