D
Deep Frayed Morgues
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!
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!