Optimize copy/paste loop

I have a matrix-worksheet that is approximately 100x100 cells. May grow to 100x400

I need to write a macro that cycles through all the cells and if it finds a specific value, it will copy the cell value to a new table. Speed is a problem right now. Suggestions for improvements appreciated. Only a small percentage of the cells actually contain needed data.

This spreadsheet contains a lot of formulas (index, match, etc), so I wonder if the macro is slowed by recalculation on the worksheets??

Note: I am not a programmer or excel expert.

Current code:
*****
Sub copymatrix()

Dim i,j,counter as integer
Dim TestValue as Single
Dim tblsource, tblDestination as objects


'//Begin Code
Set tblsource = Worksheets("Sheet1")
Set tblDestination = Worksheets("Sheet2")
i,j,counter=1 '//initialize counters

'// Loop through the matrix
for i = 1 to 100 '//note: i actually use variables to set lower/upper loop bounds.
for j = 1 to 100

Testvalue=tblSource.Cells(i,j).value

if TestValue = "LogicalTest" then
tblDestination.Cells(1,counter)=TestValue
tblDestination.Cells(2,counter)=Counter
tblDestination.Cells(3,counter).value=TestValue+5
counter=counter+1
endif
next j
next i

end sub

Hello aldsv,

The changes below should help speed things up for you. Using specific object declarations instead of generic helps improve speed. Changing tblSource and tblDestination from Object to Worksheet, and changing i and j form Integer to Long. All cell addresses on the worksheet are Long. Over all, you did a good job for not being a programmer.
_________________________________________________________________

Amended Code:

Sub copymatrix()

Dim i As Long,j As Long,counter as integer

Dim TestValue as Single

Dim tblsource As Worksheet, tblDestination as Worksheet


'//Begin Code
Set tblsource = Worksheets("Sheet1")
Set tblDestination = Worksheets("Sheet2")

counter=1 '//initialize counter

'// Loop through the matrix
for i = 1 to 100 '//note: i actually use variables to set lower/upper loop bounds.
for j = 1 to 100

Testvalue=tblSource.Cells(i,j).value

if TestValue = "LogicalTest" then
With tblDestination
.Cells(1,counter).Value =TestValue
.Cells(2,counter).Value =Counter
.Cells(3,counter).value=TestValue+5
counter=counter+1
End With
endif
next j
next i

end sub
_________________________________________________________________

Keep up the good work,
Leith Ross

Leith--Thanks.

I was hoping there was a magical "Convert Matrix-to-Column" function.

I don't suppose reading all the TestValues into an array, then writing them all at once on the tblDestination would improve the speed?

My code writes back and forth between two worksheets one test-value at a time.

Solution Found (for anyone who googles the same problem):

Each copy/paste was interrupted by the spreadsheet recalculating. I turned off calculation, then turned it back on at the end. Things run fast now.

Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic 'Turn on calculation again
Application.Calculate