Copy data from sheet1 to sheet2

[BigBlackMamba]

How do I copy data from one sheet to another - the first sheet has a fixed range of data and I need to copy this to sheet2 so that data is added to the first blank row in sheet2. Also, how can I code this so that it does this periodically (every 30 seconds)?

[jaslake]

Hi BigBlackMamba

What happens to the data on sheet1 after it's been copied to sheet2? Does it get erased? Does it stay on sheet1 and get marked as processed? If it gets marked, what column?

You indicate you want this process to run every 30 seconds. I don't know the source of the data for sheet1 and I don't know the consequences of running this procedure when sheet1 data is being updated. But, it can be done.

A sample of your data would be nice.

John

[BigBlackMamba]

Hi BigBlackMamba

What happens to the data on sheet1 after it's been copied to sheet2? Does it get erased? Does it stay on sheet1 and get marked as processed? If it gets marked, what column?

You indicate you want this process to run every 30 seconds. I don't know the source of the data for sheet1 and I don't know the consequences of running this procedure when sheet1 data is being updated. But, it can be done.

A sample of your data would be nice.

John

Thanks for responding.

The data in Sheet 1 is will not get erased or marked as updated - it's streaming data from a data supplier. All I'm trying to do is take a snapshot of this data as fast as possible. The second sheet will be copied to a third sheet in a summarized form and then erased from sheet 2.

I don't have any sample data to show you as yet.

Are there any limitations on how fast Excel can copy this data (from sheet1 into 2)? e.g. could this be done every second or 2 seconds?

[jaslake]

Hi BigBlackMamba

Put this code in the Workbook Module

Please Login or Register  to view this content.

Put this code in a general module

Please Login or Register  to view this content.

A sample book is attached for your perusal. The code is set to run every 2 seconds.

John

[BigBlackMamba]

Thanks - that's really good!!

[jaslake]

Hi BigBlackMamba

What you have may work well in a static world (sheet 1 is not being updated). I've no idea what happens when sheet 1 is being updated the same time the procedure runs on the timer. Guess we'll find out.

If your immediate issue is resolved, please mark your post as "solved". A click on the scales is appreciated if you feel it;s appropriate.

John

[BigBlackMamba]

Hi BigBlackMamba

What you have may work well in a static world (sheet 1 is not being updated). I've no idea what happens when sheet 1 is being updated the same time the procedure runs on the timer. Guess we'll find out.

If your immediate issue is resolved, please mark your post as "solved". A click on the scales is appreciated if you feel it;s appropriate.

John

Yes indeed......I ran it over static data with 700 rows and it worked very nicely but the acid
test really will be with streaming data......fingers crossed!! Thanks again for your help - I suspect you've written one or two programs in your time