Hi

The code snippet below works too slow. It exports data from one Excel table
(Customer,CustCode, Article, January, ..., December) to new one in different
workbook (CustCode, Article, FirstWorkdayOfMonth, EntryType, CustCode). It
looks like the problem is writing data to new table - writing 12 months data
(12*5 fields) from a row in source table into new table takes ~50 sec. As
the source table has almost 2000 rows, and most of rows have values for all
12 months, it takes 10 hours to generate the new table. (The rest of code
takes next to nothing to run - the code row displaying process info on
status bar was added to check this)

At start I used offset to determine target cells, and suspected this to be
the cause. So I replaced offsets with direct references - but this had no
effect at all.

Can anyone explain me this? Thanks in advance!

....
k = 0
....
If
Workbooks(varSource).Sheets(varSheet).Range("A1"). Offset(i - 1, 1) = varCode
Then
varArticle =
Workbooks(varSource).Sheets(varSheet).Range("A1"). Offset(i - 1, 2)
For j = 1 To 12
If
Workbooks(varSource).Sheets(varSheet).Range("A1"). Offset(i - 1, j + 2) 0
Then
Application.StatusBar = "Code: " & varCode & ",
Article: " & varArticle & ", Month:" & arrMonths(j)

ThisWorkbook.Sheets("Forecast").Range("A" & (k +
1) & ":C" & (k + 1)).NumberFormat = "@"
ThisWorkbook.Sheets("Forecast").Range("D" & (k +
1) & ":E" & (k + 1)).NumberFormat = "General"
ThisWorkbook.Sheets("Forecast").Range("F" & (k +
1)).NumberFormat = "@"
ThisWorkbook.Sheets("Forecast").Range("A" & (k +
1)) = varCode
ThisWorkbook.Sheets("Forecast").Range("B" & (k +
1)) = varArticle
ThisWorkbook.Sheets("Forecast").Range("C" & (k +
1)) = arrMonths(j)
ThisWorkbook.Sheets("Forecast").Range("D" & (k +
1)) = Workbooks(varSource).Sheets(varSheet).Range("A1"). Offset(i - 1, j + 2)
ThisWorkbook.Sheets("Forecast").Range("E" & (k +
1)) = 4
ThisWorkbook.Sheets("Forecast").Range("F" & (k +
1)) = varCode
k = k + 1
End If
Next j
End If
Exit For
End If
....


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )