UserForms with basic copy Paste Running Slow

    Hi everyone,
    Well lots of regards to all members of this forum.....The Best I have come across.
    I have a very basic workbook for inventory control for my shop. just have to give some finishing touches to it.....Thanks to you all for your help.
    Among many other user forms and command buttons in the workbook the following three (although they work fine and give the desired results) Run VERY SLOW.
    Maybe its got something to do with the codes.
    I need to speed up the codes and streamline them.
    Please help
    1) "Day Close" command button.......This closes the day, copies the sheet to a new worksheet with the current date, and clears certain ranges etc
    2)"Update and Add" Command Button.......This updates, adds new items to the inventory and can modify certain values etc
    3) "OK" command button........During counter Sales the sold items, quantity, their rates and total sale amount is registered in the Registry section for further action.


    All these codes are functional and give the desired output, but i am not satisfied withe speed of operation.
    Please if possible can someone see if there is any room for modification and streamlining of the codes.
    I am attaching the workbook for your reference.
    Ill be grateful, as i need to implement this in my shop at the earliest.
    Thanks

    Do you have a master data sheet in the workbook or do you intend having 365 sheets?


    From what I can see in the example without a master data sheet it's not really going to be a very good inventory workbook.

    Hi Roy, Good evening...
    This is a very simple inventory workbook and it does not have a very large data. A maximum of 200 brands in a very small establishment. the inventor is taken on day to day basis and once the sale records are uploaded and the month closed , I start with a new workbook. The data is present in the worksheet itself and at day close in carried to the next worksheet till the end of the month and has so far worked fine and is serving its purpose.
    I am just three months old in excel Vba and have gathered bits and pieces of code from the web, so am not much versed with the high ends of coding.
    Please advise
    Regards
    Zubin

    An Inventory workbook doesn't need multiple record sheets. I'll attach some examples when I get home.


    I'm not entirely sure what you are doing but I think the code could be streamlined.


    Why is the range ListOfData covering such a large area, including empty rows,and unrelated data? The empty rows are reflected in the ListBox, when you scroll down you run out of products. That should be the extent of the |ListBox

    Hi Roy
    First and foremost, I have just send you a part of the workbook due to upload limitations.
    The ListOfData range is used in the userform listbox for inventory update and adding new entries. I have kept this range large, so as to be able to add maximum number of inventory items in future as and when needed.
    Secondly you have mentioned of multiple record sheets. well this I myself am not sure why i have done this, but one reason i can think of is, this way i can update the inventory data such as pricing, taxes etc (which keeps changing frequently) on the day it takes effect and day to day basis. Please note I am just a beginner with no prior knowledge of VBA and when i started this was the only way i could think of.
    Ok now the main function of this workbook is to scan the items sold at the Sale counter of my shop (using a bar code scanner) and later upload the sale figures on to the government website as per their given format. The bar code scanning is done in the RangeB4:B14 and on Pressing OK the sale data is stored in the sale registry below. These sales are reflected in column "AU". there is a command button "Upload Data" which picks up the sale figures and other required data and copies it to a new workbook, and this workbook is in turn uploaded to the net (this is done manually)
    Although this procedures are very primitive and basic in nature, I am getting the desired output except for the slow speed i get in certain codes.
    Added to all this I have certain columns in the SetUp section which calculate the taxes , landing cost, Profit per item etc and is shown in a user form named analysis in the update and add section.
    Please guide me how to go further
    Thanks a lot for devoting your time
    Regards
    Zubin

    I think your data range is too big to be manageable. I've looked at your userforms and the only thing that I can see that might possible slow the workbook is when you are loading the form.


    At what point does it seem to slow? Which button are you clicking in?


    The data should be in one table and calculations based on that table could be in a separate range.


    Here's an example of an inventory sheet using formulas

    Hi, Roy....
    Nothing can be done about the data Range...All parameters are required for future date when i plan to add the financial and tax calculations in the workbook.
    As of now the "Day Close", and "inventory Add and update" codes work slow. The inventory Update code that is when parameters are changed and the "update" command button is clicked in the user form... is very slow....may be the data is large, but doubt that. same with "dayClose"
    I doubt the codes are making the process slow.
    Ok thanks
    Regards

    You really need to split the data into relevant tables, that's how a true database works. I would do it now before it becomes too complicated, but that's your choice.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login