Posts by netanel99

    hello excelers,

    I've made a todo list with some help with other sources, and the vba there is simple but still it's not mine,


    Is there any chance you guys can perfect it just by adding VBA to delete complete todo's (those who have V next to them) after X days ? (the user will write how many days after completing or after the targer date for the todo and V for completing the row will be deleted)


    If you guys can see any improvement to this I'll love you guys to add magic to it. just this though will be amazingly enough , and thanks for any help at all,

    best regards,

    Netanel.

    BTW- I'll just translate this file back to hebrew for now it's mostly english.

    hello experts,

    I just have something like 400 excel files that I wish to not deal with auto text wrap, this function isn't smart at all in my opinion and all it does is making me lose time be readjusting it repeatedly,

    is there any amazing awesome life saving tip to it ?

    I can't believe I'm in excel for such long time and I never consider doing something about it,

    thanks for any help you guys ^^

    Netanel

    Hello dear dear Excel geniuses ,

    the more I improve the more I understand how awesome you are,


    I'm building Projects management planner in excel ,

    I have the table which have all my tasks , and outside of the table to the right I have columns of dates, and the cells shows the number of minutes that a specific task will take, and I'm doing Sum on that and getting the sum minutes I'm working daily on all that particular date projects.

    unfortunately I got 2 problems : 1 big problem and 1 medium-small problem.

    My biggest problem is that whenever I delete a row or adds new rows - the right calculations get Ref error, I know that index might fix it but I don't know how to drag formula with index and my second problem is that I wish that this is could have being much more resources friendly because I want those right dates to be like 10,000 columns of dates but the file get too slow.


    I wish pls to keep automatic calculations .


    I've uploaded my project management file, I cut it to 450 kb , originally it's 20MB because of many dates and calculations beneath.

    other than this project management is almost finish, I guess I know how to do format conditioning to make those start date till end dates colored.


    best regards and thanks ahead,

    Netanel.


    Project Management Planner.xlsm

    oh , I have a code in other workbook that copy paste one of those workbook with inside automatic code, when the other workbook copy paste this file, it doesn't work, it just open VBA first and gives runtime error 1004, then I click ok and then it opens the file. then I can open it and it works again , but this fix it:

    Worksheets("template").Activate

    I just bummed by the fact that it has to stay "template" named worksheet

    Thanks for the help .

    Interesting this what this line of code need to do, which it didn't showed

    Code
    Private Sub Workbook_Open()

    Just wondering If what I've entered before the code canceled the active workbook select effect:

    Code
    Application.ScreenUpdating = False
    If ActiveWorkbook.Name = "NextLevelCareer.xlsm" Then
    Application.Goto Reference:="MakeCode"
    Selection.ClearContents
    End If


    although the line of code for activating the workbook help, it have to be "template" sheet which can make problems .


    This is the whole code inside "this workbook" main automatic VBA :


    without

    Code
    Worksheets("template").Activate

    it doesn't work well

    I've noticed that this error doesn't happen when In the beginning of the code:

    Code
    Worksheets("template").Activate


    but can I just activate this workbook/sheet without naming it ? because if the user'll change the name of the sheet from the generic name of template to something else it will cause an error,

    the workbook is just being made and open, how can I activate it without choosing name for it ?


    thanks in advance.

    hello experts,

    I've an automatic macro that initiates when the file opens, it works perfectly when I just open the file, but it doesn't work if another not related macro create this excel file and opens it, in this case it gives and runtime error 1004 - method range of object global falied


    and yellow row marked : "If Range("MakeCode") = "" And ActiveWorkbook.Name <> "NextLevelCareer.xlsm" Then"


    it looks like the code is failing before the file get the chance to open, but if I just click on the file it opens first and then initiate the code perfectly, if this is the case how can I fix it please?


    thanks for any help.


    Quote

    Thanks Roy!

    Pls, how can I sign this beauty to give value ?

    I was trying


    Code
    sub FunctionGiveValue ()
    dim GetNewestFolderValue as string
    
    GetNewestFolderValue = GetLastFolder ("ThisWorkbook.Path & Application.PathSeparator & "library"")     ' failed
    
    end sub

    I was trying to use it with dynamic path instead of static one ("C:\folder") which unfortunately failed


    thanks for all,

    Netanel.

    I was thinking on a second possible solution that will be also amazing -that instead of finding the last edited/created folder inside a folder and copy-paste it , there will be a systematically naming of the new folder instead of asking the user for a name , like this:

    Code
    ans = "new folder" & " - " & Format(Date, "dd-mmm-yyyy")

    so the code could search for the folder with the highest value- the latest number if that possible (because I need the folder with the most recent date )- so the newest folder always will get picked and the code will copy -paste the last one. which I have no idea how to do but I was keep thinking on it for the last who knows how many hours.



    Thanks ! Thanks ! THANKS!!!

    Be Blessed.

    Thanks Roy , it was a journey , I really made new insights, understanding and skills in VBA .

    sorry for the delay, my laptop fell and I got short circuit in one of the mother board component, this is my older laptop that I just furnished .


    My last piece in the puzzle is making this exact code work for a folder, that means instead of finding the last edited/created file in a folder the code will find the last edited/created folder in a folder - and then copy paste it .


    Is it possible as a finisher bro ?
    I'm on it, really surprised myself with VBA in the last week, but I think I need one last help in this one.


    best regards,

    Netanel


    I've tested the whole code, it works pretty good unless the user choose a name with "." , then it creates a file with no excel extention ".xlms". (like: new name 2.4.21 - that's it without .xlms)

    Is it possible to change the code that instead of asking the user for a name, the code will choose a name automatically , and the name will be "new name"+Today date ? that will be seriously awesome.

    for example executing the code today will give a name of:

    New Name 3.5.21

    and tomorrow will be

    New Name 4.5.21

    and so on .

    just wondering if that possible with fixing the problem that the code doesn't give excel extension when a "." involved in the name.


    it makes the whole code bitter sweet .

    thank you already for all the help .

    Netanel


    Quote

    It should be saving into the folder called library.

    yes , it is , the code working good just need 2 adjustments.


    Quote

    Do you ant to open a template workbook not the last modified?

    Right now it open the last modified, but I want it to open the last created. (and also copy paste it and everything evolved this code on last created instead of last modified)

    And only in a case of error, I'm thinking that sometimes I move files from hard disk to hard disk and it doesn't save the last created date on the excel files, so I'm thinking maybe in this case the code will give an error, so I wonder what you think and if it will be smart to add "if" to the code that in a case that the code doesn't find last created date excel file that it'll open specific excel file in the "library" called "template.xlms" ?


    thank you for your patient brother.

    best regards,

    netanel

    I did the last part of the code, I don't know how, but it works, the code add a new row in the table, add the hyperlink to the new excel file and opens the new excel files,


    Is it possible to make the code copy-paste the last Created excel file in "library" folder, instead of last Modified excel file ? I just paid attention to it and it will make trouble down the line because the real intent is for the last created file.

    Is it possible in a case of an error when the code search for the last edited/created excel file , that it will open simply the file "Template.xlsm" please ? (just to make sure that the code will still work even if there'll be an error, sometimes I move files from one hard disc to the other and the all files get the same date created/edited)


    best regards,

    Netanel



    last code working, with my add on .

    Wow that's an interesting question to speak about, because I've found that , at least in my opinion , this holds an unbelievable efficiency and productivity power that goes long long way afterwards,

    For me excel is the real power house for database management , I don't use Access, that because I just have a table that connect with hyperlinks to different excel files and just draws the important data from the other excel files to the table(and if I change something I update)- while keeping my main excel file with the organizing table running like a rabbit with all the power of excel instead of the unfriendly Access, for me its a perfect win win situation , If you like I can show you my work with deeper insights on Zoom .

    Here, it's a special database that focus always on the last file, because there's an ongoing update action that takes place on the newest file all the time.


    I hope that I didn't explained wrong ,please I've added pictures to illustrate what I'm asking ,

    Thank you for being the only one who helped me in this problem




    Thank you so much Roy!

    The code successfully asks for a name for the new excel file, then locate the latest excel file inside the folder and copy- paste it, giving it the name which the user has chosen.


    Is it possible please to add to it that the new excel file will be open and the code will make a new row in the table, with a hyperlink to the file ?


    I was trying to integrate this to do it but I'm not really good at VBA and it failed

    Code
    With ThisWorkbook.Sheets("Strategies Contents").Range("B" & Rows.Count).End(xlUp).Offset(1)
        .Value = StrategyName
        ActiveSheet.Hyperlinks.Add _
            Anchor:=.Offset(, -1), _
            Address:=NewName, _
            TextToDisplay:="Link"
        Workbooks.Open NewName