How to implement multi-threading and problem specific questions in vba

Question

I have a spreadsheet with about 25 sheets. In some of the sheets specifically 6,7,13,17,18,19 I'm populating them with Database values. In some sheets I'm populating two table's data. Now I need to create a button which will do the following operation. On the button click event I need to traverse through all sheets and see if any sheet is filled.If it is filled then copy those contents to a file.I've already implemented coping one sheet contents to a file.

Question 1) I cannot understand how to do for multiple file.

Question 2) How to record that a particular sheet has been populated with two table values.Since I would be needed to make one file per table.

Question 3) Most importantly This will take a lot of time, hence I want to fasten my operations, so I want to do Multithreading. Any other way to improve speed would be great.

Another thing in general is, I would like to thank a loy of people for their help.ANy way where I can make post or blog about thid

Answer 1

There is a large time overhead whever a program reads data from Excel. This is probably the most frequent cause of complaints about speed.
To minimise this overhead you need to transfer as large a block of data as possible in each Read.
In VBA you do this by assigning a Range to a Variant:
Dim vArr as variant
vArr=Range("A1:Z5000")
The vArr will now contain a 2 dimensional array of 26 columns by 5000 rows.

Using .NET via Interop this technique is even more important because the time overhead is even larger than VBA.