Posts by Jacolene

    Re: Automatically Update Data using VBA Code

    Hi Cytop, I think I like your option 2 best, please advise what you mean by an identifier?

    I have 38 sheets, exactly the same, but with different data (students information, like name, surname id number, gender etc.), so I used that code above, to combine all info from those 38 sheets to 1 worksheet, but, lets say I made a spelling mistake on a name from one of the 38 sheets, the code does not update the "combined" sheet automatically. I have to go and delete the tab, and run the code again


    Please advise if it is possible that a VBA code automatically update the data if there is change made to the worksheets?

    I am using the below code to add 38 sheets of data in the same workbook, to 1 sheet called "combined" (also in the same work book)

    But if I make changes to data on any of the 38 sheets, the sheet "combined", does not update automatically.

    So I need to know if there is a way that it will automatically update the "combined" sheet?…68&highlight=#post4395568


    So I know the formula of taking the first letter of 2 words: =LEFT(J4,1)&E4 (this calculate the course they will be doing and their gender)

    My complete formula so far is: =(Control!$G$2&"-"&Control!$H$2&LEFT(J4,1)&E4&RIGHT(Control!$B$6,1)), which looks like PKW-APF1

    What I need now is to add incrementing number 001,002,003 etc. (without having to use a helper column)

    In the end it should look like: PKW-APF1001, PKW-APF1002, PKW-APF1003 etc.

    I want to change the maximum change under options > formulas to 0.001, but it keeps showing the error: your entry cannot be used. an integer or decimal may be required

    Even my (point)/delete button on the keyboard will show a (point) in word when I use it, but in excel it shows a (comma)

    I tried the below from an old thread from excel forum, but it didn't work

    Open a new workbook
    hit alt-f11 (to get to the vbe.
    hit ctrl-r (to see the project explorer)
    locate your workbook's project.

    It should look like:

    Right click on that and select Insert|Module.
    Paste this in:

    Option Explicit
    Sub testme()
    With Application
    .Calculation = xlManual
    .Iteration = True
    .MaxChange = 0.001
    End With
    End Sub

    Put your cursor inside that macro (click on With).
    Hit F5 (to run the code).

    Then alt-f11 to get back to excel.
    Can you get to tools|Options|calculation tab
    (And change the calculation back to automatic, too.)