Posts by oddy73

    Re: Combine two data sets and merge them

    Hi jindon,

    Hopefully you are doing alright. Since our session, I learned allot and developed further our tool, especially hour comparison and merge solution. I’ve implemented and test all the way and it worked great, many thanks again for that!

    While using the comparison solution, and running reporting we missed some data… at first everything looked fine and nothing was lost. Suddenly I found the “bug”. Actually it’s not a bug, it’s a scenario that we did not think about at that time.

    The scenario that we forgot states:
    If a person works on one or more projects per month but he is not sick or he has not taken any holiday in that month. How should the comparison code should handle this scenario. Because the current code does not take that person into account while merging… see the attachment.

    For example in the attachment you see that even though Marco worked on one project and he was not sick or taken any holiday on that month, he will not taken into account while merging…

    Where should I look for modifying the code in order to take also Marco into account.

    And what if a person is the whole month sick and he/she writes zero hours for that project… how should that the code be looking?

    Re: Display date Textbox as dd-mm-yyyy


    thanks for your feedback. Your solution works perfect, exactly how I wished. I discussed this change (using Dropdown box) and unfortunately this is not what the "my boss" wants. In the meantime I developed a workaround by using three textboxs and concatenate it in excel...

    I know this is not clean. I will save this solution and use it definitely next time! I did not expected that this was that easy :)

    Thanks man!!!

    Re: Display date Textbox as dd-mm-yyyy


    After testing the solution it gave me strange results.
    I attached the example and as you may see the birth date Textbox displays correctly the date (the format), however i cannot edit it anymore... I found this a very strange bug...

    Any help is welcome :)

    (Since I did not get any response, i post the same question also here, When I find the solution, i will post it here off course :) )

    Re: Code gives error when column is empty

    This works now for me :)


    I have this code that works perfect (almost). It copies all the data in column A, B,C, etc... and pasts under A2. However, it does not work if a column is empty.
    What I wish is that this code ignores empty columns and continues collecting columns and pasting it under A2, because I have another macro that removes duplicated and blanks :)

    I have provided an example attachment with the button .

    I waited and did not get any response so I posted also here.

    Hey guys,

    I live in Europe and we I use for the userform the following date notation: dd-mm-yyyy

    My userform is inspired from the following source

    The user puts a date like: dd-mm-yyyy for example (06-04-2011) (April)
    This userform saves the date like dd-mm-yyyy. However when I restart the userform and I look up the data, the date displayed as mm/dd/yyyy.
    The funny part is when I change my regional settings to dd-MM-YYYY and the useform is already open, than when I look up data, then it displays how I wanted: dd-mm-yyyy. But when I restart the excel and look up the data, it goes back to mm/dd/yyyy.

    My wish is how can I force the textbox to display the date as dd-mm-yyyy.

    (tip: you can use the ID to look up data in the userform)

    Thanks in advance

    I start this new thread since my main problem had different title and different problems. Here is the link to that other solved thread.

    I have 10 worksheets (and growing) in my workbook
    The first four worksheets I use them for some calculations via a macro . And via this macro I create more worksheets for example 6 taps (4+6=10 tabs).

    I have the following code, which exports all the worksheets in this workbook in to the same folder where the main file exists. This is not what I wish.

    My wish is to have the possibility when using the a button (SaveShtsAsBook) that I get immediately a dialog box where I can select my destination than only the worksheets that are created (6 worksheets) should be exported and not the first four. Once the export is finished, all the visible (because I have also hidden sheets) worksheets should be deleted and only the first four worksheets should remain. Yes the first four worksheets can be hard-coded since its name never changes...


    Re: Run macro in another worksheet (Advanced )

    Hey guys,

    I promised you all to give an update. I succeeded to pass the tabname variable to the random “ButtonGenerate”. Now the function "ButtonGenerate" is applied for each tab.

    I also succeeded exporting each individual created tab into a folder. However this is done very badly…

    Right now, the export function exports all the sheets of the current workbook and creates a folder and export them.

    My wish is to have the possibility when using the “Merge & Report” button that I get immediately a dialog box where I can select my destination and then the process continues by distributing (+"ButtonGenerate") everything.

    Once everything is distributed and exported, the main file with all the new created tabs should get removed (be ready for next month). So only two tabs should be there à Merge and Cal_template.

    Can anyone help me finalize this reporting tool?


    Re: Run macro in another worksheet (Advanced )

    Hey guys,

    I've came very far and now I am stuck. Here is how far I came:

    • Implemented a new calendar timesheet
    • Implemented a macro that generates random numbers for that person, based on the data we have.

    Since couple of days I cannot fix the issue where I can give the macro “ButtonGenerate” the variable of the current or active sheet. Right now its hard-coded for “Rob”. And as you can see, the button generates random numbers only for “Rob”. I tried to pass the variable “e” but had no success. Question is how to generalize the macro “ButtonGenerate” so it can be applied for all the created tabs?

    Another issue I have is that I am trying to add another function in this whole process, immediately at the beginning.

    So when the user clicks the button “Merge & Report”, he should get first a save as dialog box where he ONLY have to choose (or create a folder somewhere) the folder. Once the folder is created, the process starts by 1 merging, 2 distributing the data over each person, generating random numbers and finally all the created tabs should be exported to the folder that was created at the beginning of this process.

    I hope I am clear to what I want to achieve :)

    Thanks in advance!

    Re: Run macro in another worksheet (Advanced )


    Sorry for late feedback... the weekend started and other responsibilities come alone with it.
    I have implemented the code jindon and it works very nice and smooth...

    Right now I am trying to modify and tweak the code, like removing the formulas and keep the values in the end reporting (per person). Another thing that needs to be done is also run a macro per sheet.

    I will update you guys and let you know.

    Many thinks everyone!!!!!!!

    Re: Run macro in another worksheet (Advanced )

    Please find attached an export of Rob.
    However the macro I wanted to run on step 3 should delete column A to F and somehow maintain the data in the timesheet. But that is a challenge for later.
    (And if possible to give the tab name a date )

    royUK, I tried your code it copied only exported cal_template and applied formatting, it did not copy any data from the merge sheet in it.

    Thanks guys :)