Slow code operation when refering to different sheets
 

Hi folks,

I have the following code which takes values from Worksheets 2 to 8,
and writes them to Worksheet 9. It works just fine, except that it
runs quite slowly, copying only about 2 rows a second. From
observation, the lines within the If Then statement is what is slowing
it all down, possibly because I am copying to and from different
sheets. Any clues as to how I can speed it up easily?

===========================================
Function CopyDataToFinalTab()

CurrentOutputRow = 2
For MyWorksheets = 2 To 8

x = 5
Do While (Worksheets(MyWorksheets).Range("J" & x).Value < "")
If Worksheets(MyWorksheets).Range("J" & x).Value = "y" Then
Application.StatusBar = "Worksheet:" &
Worksheets(MyWorksheets).Name & ", Row:" & x
Worksheets("DataFile").Range("A" & CurrentOutputRow).Value
= Worksheets("Control").Range("B7").Value
Worksheets("DataFile").Range("B" & CurrentOutputRow).Value
= Worksheets("Control").Range("B8").Value
Worksheets("DataFile").Range("C" & CurrentOutputRow).Value
= Worksheets(MyWorksheets).Range("A" & x).Value
Worksheets("DataFile").Range("D" & CurrentOutputRow).Value
= Worksheets(MyWorksheets).Range("B" & x).Value
Worksheets("DataFile").Range("E" & CurrentOutputRow).Value
= Worksheets(MyWorksheets).Range("B2").Value
Worksheets("DataFile").Range("F" & CurrentOutputRow).Value
= Worksheets(MyWorksheets).Range("I" & x).Value

CurrentOutputRow = CurrentOutputRow + 1
End If

x = x + 1
Loop
Application.StatusBar = "Complete."

Next

End Function
================================================== ====

Thanks very much folks!
---
DFM

Slow code operation when refering to different sheets
 

Hi
test if
application.screenupdating =false
at the beginning
and
application.screenupdating=true
at the end helps to speed up the processing

"Deep Frayed Morgues" wrote:

Hi folks,

I have the following code which takes values from Worksheets 2 to 8,
and writes them to Worksheet 9. It works just fine, except that it
runs quite slowly, copying only about 2 rows a second. From
observation, the lines within the If Then statement is what is slowing
it all down, possibly because I am copying to and from different
sheets. Any clues as to how I can speed it up easily?

===========================================
Function CopyDataToFinalTab()

CurrentOutputRow = 2
For MyWorksheets = 2 To 8

x = 5
Do While (Worksheets(MyWorksheets).Range("J" & x).Value < "")
If Worksheets(MyWorksheets).Range("J" & x).Value = "y" Then
Application.StatusBar = "Worksheet:" &
Worksheets(MyWorksheets).Name & ", Row:" & x
Worksheets("DataFile").Range("A" & CurrentOutputRow).Value
= Worksheets("Control").Range("B7").Value
Worksheets("DataFile").Range("B" & CurrentOutputRow).Value
= Worksheets("Control").Range("B8").Value
Worksheets("DataFile").Range("C" & CurrentOutputRow).Value
= Worksheets(MyWorksheets).Range("A" & x).Value
Worksheets("DataFile").Range("D" & CurrentOutputRow).Value
= Worksheets(MyWorksheets).Range("B" & x).Value
Worksheets("DataFile").Range("E" & CurrentOutputRow).Value
= Worksheets(MyWorksheets).Range("B2").Value
Worksheets("DataFile").Range("F" & CurrentOutputRow).Value
= Worksheets(MyWorksheets).Range("I" & x).Value

CurrentOutputRow = CurrentOutputRow + 1
End If

x = x + 1
Loop
Application.StatusBar = "Complete."

Next

End Function
================================================== ====

Thanks very much folks!
---
DFM

Slow code operation when refering to different sheets
 

"Frank Kabel" wrote in message ...
Hi
test if
application.screenupdating =false
at the beginning
and
application.screenupdating=true
at the end helps to speed up the processing

Exactly what I had been looking for. Thanks!
---
DFM