ActiveSheet is not the ActiveSheet?

[sjc]

I'm trying to use a button in one workbook to open another Excel file, copy data from that file into an array, and paste some of the array data back into the original file (with the button) but on a different sheet than the sheet where the button is located. In the past, I've had no problems with this. I was just "updated" to Excel 2010 though from 2003 and things don't seem to work as they always have. Anyway, here are the basic steps:

1. Click button in Workbook A on Sheet 1

No problems with button activating macro in this step

2. VBA opens Workbook B (only one sheet) and reads all the data into an array

No problems with this step

3. VBA switches back to Wrkbk A and selects Sheet 2

I was using: Windows(<i>Wrkbk A</i>).Activate
Sheets(<i>Sheet 2</i>)).Select

I had problems in the next step though, so I switched this to:

Windows(<i>Wrkbk A</i>).Activate
ActiveWorkbook.Sheets(<i>Sheet 2</i>)).Activate

This seems to be working alright.

4. Clear old data from Sheet 2

I was using a Range().ClearContents command but it was clearing the data in the specified range on Sheet <b>_1_</b>, not Sheet 2.

So I switched to: Sheets(<i>Sheet 2</i>).Range(<data range>).ClearContents

This seems to be working alright.

5. VBA goes through the array and all records meeting a certain criterion have their data written to Wrkbk A, Sheet 2.

At first, as I cycled through the array, I was trying to write to Sheet 2 using a series of Cells(<i>Row, Column</i>).Value = DataArray(I).<i>parameter</i> statements. Even though I'd selected Sheet 2 though, it was still writing to Sheet 1.

So, I put in another 'ActiveWorkbook.Sheets(<i>Sheet 2</i>)).Activate' statement.

This didn't help though. So I changed all the write statements to have the form:

ActiveSheet.Cells(<i>Row, Column</i>).Value = DataArray(I).<i>parameter</i>

This now seems to be working (writing to Sheet 2), but it is INCREDIBLY slow. Each value takes about two seconds to write out. Since we're talking about somewhere in the neighborhood of 700,000 values to be written out, this is not acceptable if I want to finish before I retire (I'm 37).

So, I have two questions:
(1) Why does it seem that Excel isn't keeping track of the active sheet like I remember it doing in Excel 2003? Did something change? My memory is pretty good and I never had to continually specify the active sheet if I'd already done it once. I looked at a script I wrote a couple weeks ago to do a similar process and didn't have to fuss at all with specifying active sheets anywhere.

(2) Why is the write process from the array to the worksheet so slow?

Any advice would be fantastic. Thanks everyone.

-Sean

 

[Norie]

Could it be simply that the code is writing each value individually using a loop?

Using arrays can speed up code but I think the idea is you write the entire array in one go rather than item by item.

There also seems to be something called YldRec which I think is some sort of user-defined type or collection.

Have you considered using 'standard' copy and paste for this?

It would probably be slower than arrays but not that slow - you wouldn't need the loops to populate or write the array(s).

I could be totally wrong of course but I'm not sure what sort of data you are dealing with.

There's one loop I really don't think you need:

While ActiveSheet.Cells(I + 2, 1).Value <> ""
            I = I + 1
        Wend

If this is meant to find the next empty row in ActiveSheet it can be done in one line.

I = ActiveWorksheet.Cells(Rows.Count).End(xlUp).Offset(1).Row

Apologies if I'm getting this completely wrong, perhaps if you posted an explanation in words what the code is meant to do it might help.

 

[sjc]

Thanks again. I did end up using:

Wrkbk1.Sheets(A).Range("A4:AQ" & NumNewRecs + 3).Value = Wrkbk2.Sheets(A).Range("A2:AQ" & NumNewRecs + 1).Value

But what really made the difference was turning off the auto calculation:

    Application.Calculation = xlCalculationManual   'at the beginning

    Application.Calculation = xlCalculationAutomatic   'at the end

Now it runs fairly quickly. I should have thought of this earlier