Clear contents from range and move data up

  • Have drafted for you the very first version ... based on my understanding ...;)


    1. With a mouse Double-Click in Column Contact Date : the Current date is added

    2. With a mouse Double-Click in Column Start Time : the Current time is added

    3. With a mouse Double-Click in Column End Time : the Current time is added

    4. The Duration Call is calculated automatically in Column E

    5. The Cumulative Duration for all Calls is calculated automatically in Column H

    6. An automatic call counter is added to cell K1

    7. All 22 macros have been merged into a single one to adjust all row heights

    8. All macro modules have been deleted, and merged into Module 1


    Edit : Sorry ... Our messages just crossed ... will review all your latest comments... Thanks

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Yeah i think so... haha. Imagine an organisation that deals with the public day in day out. we receive a lot of calls, but if a client is not happy about the service we have provided them, they can complain to our complaints department and ask for a copy of all calls they made and received which we have to provide under data protection law.


    We pull all the calls off our telephony system to be burnt on to CD, sometimes we only have a handful of calls, other times we have a lot depending on the person requesting them.


    The historic call information is then put on spreadsheet in question and saved, as per my last attachment. Then the process of making CD Covers are created and calls burnt to Disc in the process.


    Hope that is a bit more helpful, sorry.

  • OK ...


    Does it mean that Data which goes in Columns B,C, D and E ... is the result of Copy Paste from another source ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • It’s manually typed on to the spreadsheet by hand from another source, due to the telephony system being on a different computer system altogether.

    They pull the list of calls up on the telephony system, and transfer that data over by hand on to the other screen, I.e the spreadsheet in question.


    two screen one desk system, if that makes sense. One is solely the telephony system, and the other is the normal office pc system that has office 2016 on it.

  • Tough and quite repetitive job =O


    In addition, from what I gather in your sample file ...Duration in Column D is .... TEXT ...!!! :cursing:


    How on earth can you work out the 80 Minutes Limit ...????:S

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Yep, which is why I’d like to automate the final process.


    The cell doesn’t need to be text, as long as the cells B:F can be manually populated. What ever you think is best. 😁


    When burning the disc, you’ll drag all calls over for burning and Nero will tell you how many calls per disc will be burned. That’s how we know where to put the X, for the CD cover.

  • Let me know what you think ...


    Could column G be used to work out the Cumulative Duration ...?


    The Button CD will indicate "CD" on the row where the Cumulative Duration is closest to 80 Minutes ...


    Hope this will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • So ...


    Could the first step of integrating everything into a single macro be the identification of "CD" in Column H ...?


    Should the counter in cell J1 display the Number of Calls stored in the identified CD ... ?


    In your ideal world, should the whole process be a complete loop to deal with all the records in one go ...


    or do you prefer to rely on a manual intervention for each step to control each level of the process ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Could the first step of integrating everything into a single macro be the identification of "CD" in Column H ...? Yes it could be


    Should the counter in cell J1 display the Number of Calls stored in the identified CD ... ? That's optional if fully automated.


    In your ideal world, should the whole process be a complete loop to deal with all the records in one go ... Personally yes, but depends on the people using it.


    or do you prefer to rely on a manual intervention for each step to control each level of the process ...??? Once again depends on the people using it in which case keeping the calls stored cell.


    I have moved the buttons on my original project, and added your print and delete buttons into the process. I'm getting a debug on your buttons.

    But everything else is working how it should.

    Files

    • CD Cover.xlsm

      (48.09 kB, downloaded 43 times, last: )
  • To recap, your objective is to print all the labels for whatever number of CD's is required to deal with all the records which are copied into the worksheet ...


    Would you be considering for each CD ... (i.e for each Label) to Print out :

    1. The CD sequence number (#1, #2, etc ...)

    2. The total numbers of calls

    3. The total duration of these calls

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Every single call that is made or received is put on the spreadsheet in question.


    A CD can only hold 80 minutes worth of audio.


    I want to do a CD label for blocks of calls that do not exceed the 80 minute limit.


    every label will have a starting sequence of 1,2,3,4,5 etc. As when you put a disc in a CD player it starts with 1.


    I don’t need the total number of calls, this is already listed on the spreadsheet then saved in a folder prior to doing any work on the cd covers.


    I also don’t need to see the total duration of calls.

    As long as the process in itself works, the viewing the total duration is not necessary. Hope that helps. 😁👍🏻

  • Attached is your test file Version 2


    You will need to add or remove Apostrophes in order to have active and inactive instructions as needed ...


    Hope this will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello,


    Just noticed : Thread is marked as Resolved


    Does it mean the macro produces your expected result ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi,


    I had marked it as resolved in error after establishing a little mistake. I have it how I want it to a point, apart from the last little thing.

    I am happy with attached but need that little bit of vba in module 24 to cut remaining cells that are populated within range B6:F205 and paste the values back on the next available row being B6:F6


    If you open up the attachment and press Next disc, you'll see my issue.

    Files

    • CD Cover.xlsm

      (42.65 kB, downloaded 32 times, last: )
  • Hello,


    From your latest message ... just found out you are still working with your Original 26 Macros - 26 Modules version ... !!!


    Basically ... with no consideration whatsoever... for all of the cleaning, streamlining and optimization suggested in the Version 2 ...


    Wish you all the Best for your project ....

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • With all due respect... What you had created does not suit my requirements. In your earlier post you created what seemed to be what I required and closed the thread, but then realised the track numbers were not as expected. I then asked is it possible to further automate, but keep a version like in my previous post just in case the staff can’t get used to it. As much as I appreciate the work you have done, I would still like to have some assistance on my own creation.


    sorry if you have taken offence, which was not intentional.