Advice - Most efficient way to retrieve NEW data from sheet and paste below existing data

Hello all wise VBA experts...

I, myself, am still getting my toes wet in the VBA world. I try to learn with each new project that I work on.

I am looking for advice as to which direction to go to pull the new data only from one sheet and paste it below the existing data.

Here is my situation... Only 1 month has gone by and I have over 5000 lines of data. Each day, the data set increases as more products are produced.

I know that I can do a loop function... basically find the last value of the old data set and loop through to find the match and then grab everything below that and paste it below my existing data set.

I have done this... it works, but I was wondering if it can be done without a loop?? as my lines will increase easily by 5k each month.

Is there an easier way that I am not aware of?

also note, that the data sets are being pulled off our PLC server and are in chronological order... there 'could' be duplicates as out of 20 machines they could start more than one machine at the same time...

I have not uploaded a sample as of yet, as I really want to try and figure this out... just need a direction to head in.

I have already done the loop, and as I said before it worked, but I fear that it will be horribly slow in the future.


Thank you for your time of reading all this and any suggestions or pointers you may provide...

I do not know what server you are referring to. If you store your data in some form of database application, you better off linking or extracting the data in to excel. For e.g. it is much faster to use ADO if you have data in SQL, Oracle or Access databases. It will take less than a minute to down load massive data. There is no need to loop.
You will run out of options once you reach around 1m rows in excel. Even looping through an array has a limit, though it is faster than looping through a range.

Currently the information I get is using an ADO connection to the server, which dumps all the data into Excel. But, every time you open the workbook, it connects to gather the data and it will erase previous data and show an updated view... from the first product produced to current.

the reason for looping through is to copy each machine's data to a sheet for each machine. Then, when it is ran the next day, only grab the new data.

I have accomplished this through using a loop, but I just fear future performance loss. Another reason for copying the data sets over from the 'dump' is that some corrections need to be made, such as human product number input errors... fixing it in the 'dump' would just get over written each day.

We keep the data to show machine efficiencies and load trends throughout the year.

My goal is to see if there is a more efficient way to gather the data that I am unaware of so that I can further my possible knowledge of VBA.

If there isn't something more efficient than looping through the cells, that is fine... I am just trying to expand my knowledge.

TMS,

thanks for the suggestion.. I will try that as a possibility and see if it helps... since I am already filtering the data for each machine, it should be easy enough to add another criteria to filter on...

Thank you.

It depends on your database. You can use SQL to manipulate the data in your data base, if it is RDBMS. IMO, using excel to store massive data is a receipt for disaster. Excel is NOT designed for storing data, particular, string data. You should explore ways on how to use SQL to link your server with excel.

AB33... I would LOVE to... sadly, it took me over 6 months for the IT department to grant us access to even read the information that we are getting now...

our old system just extracted data from .CSV dumps. it was slow, but I was able to gather the data accurately for what we needed. This new system is all in SQL, which is great... as long as they didn't treat the readability like fort knox...

Our old workbook of data from the old .CSV method has data going back to 2007... 100's of thousands of rows.. it is big bulky slow... so looking forward to something newer and hopefully faster.

All this data is just for weekly, monthly and annual reporting purposes...

Sadly, they (the IT dept), has not allowed me that ability... No certain plans to add the ability.

With the business that I do, the FAA requires 10 years of data to be kept. So each year we just start a new data set for the reporting...

I appreciate all the feedback and ideas...