Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
QUERY & HELP: so slow executing VBA code... :S | Excel Worksheet Functions | |||
Slow Code | Excel Programming | |||
Slow operation | Excel Programming | |||
Is this slow code? | Excel Programming | |||
VBA code refering to columns by name | Excel Programming |