Running VBA code to copy and paste 15,000 rows of data every 3 mins

[kavan89]

Hi,

I have 15 worksheets with 1,000 rows of data on each worksheet. Every row has 5-6 columns. I'm using a third party plug-in to automatically update the 1,000 rows - this takes roughly 3.5 - 4.0 mins. Once the newer data is available, the older data is lost and is replaced by the newer data. What I'm therefore looking to do, is run a VBA code which will do the following:-

1. Copy 1,000 rows of data from all 15 worksheets and paste the data as values in another workbook (which will also have 15 worksheets). With every update, I want the new 1,000 rows of data to be stacked below the previously pasted 1,000 rows (to ensure that no data is lost)
2. Since the time taken to update the 1,000 rows is roughly 3.5 mins, I want to update the data every 3 mins - to be
doubly sure not to lose any data due to the replacing factor
3. This exercise may/ will result in some duplicate rows. I don't mind manually eliminating these at the end of the exercise.
4. The code should run every 3 mins, but only between 9:15 am to 3:30 pm (system time)

Since the volume of data processed is large, I understand that this process will take a lot of CPU resources. Any tips to keep the process thin and lean will also be helpful.

I hope my explanation above is easy to understand. If not, I'll be happy to provide an illustration / more detailed description.

Thanks,
Kavan

P.S.: I use Excel 2013 on Windows 8.1. I have no prior VBA experience and this is my first attempt to include VBA code in a spreadsheet.

 

[mole999]

The questions will be

what are the names of the workbook where the data goes into, and the name of the workbook where the data comes from (I'm wondering why the data can't just be loaded straight to the book.)

The name of the sheets where the data is, and does it come across to the same named sheets

whilst we are on it, what constitutes a duplicate records, as I make it you will be getting 200,000 records per day, and if the import can delete them, all the better.

Maybe the addin can be tweaked to pull the data into the right location

 

[kavan89]

The workbook will have a total of 30 worksheets (15 for pulling the data using the plug-in and 15 for pasting the pulled data as values). Let's name these worksheets 01.F (for pulling the data) and 01.V (for pasting the pulled data as values), 02.F and 02.V, .... all the way to 15.F and 15.V. No need to manage two workbooks.

As explained above in brackets, the '.F' worksheets will be where the data gets pulled, and the '.V' worksheets will be where all the pulled data will be compiled.

To illustrate what could be potentially be duplicate records:
Assuming data is spread across 6 columns (viz. Date & Time, Open, High, Low, Close)
If two rows showing identical data in all its columns would be a duplicate record. For example -
Row 1 - Mar 28 10:55:55, 1.35675, 1.35676, 1.35673, 1.35674
Row 2 - Mar 28 10:55:55, 1.35675, 1.35676, 1.35673, 1.35674

I'm not sure if the plug-in can help me with that. When I last spoke to the developer of the plug-in, he asked to write some code in VBA which would enable me to automate the data compilation.

Thanks for the help,
Kavan