Poor efficiency: Code runs too long

[jwise]

I have a macro which takes data from a single worksheet in multiple workbooks and reformats it into multiple worksheets in a single workbook. Most of the time the input data is a single workbook/worksheet, with the output being multiple worksheets in a single workbook. This takes far longer than it should, and I can not figure out why. This is the second implementation I've tried. The other used "Select" and "Copy".

Here is the inner loop code. Since the problem occurs with a single input workbook, consider that the "i" loop iterates a single time. The code does work, it just seems to execute a long time (7 minutes for 42 apartment complexes). I do turn "screen updating" and "re-calc off" while this loop runs.


For i = LBound(FileName) To UBound(FileName)

FiNm = FileName(i)

Set wbMonth = Workbooks.Open(FileName:=FiNm)
Set wsMonth = wbMonth.Sheets("Sheet1")

lastRow = LastRowWithData(wsMonth)

col = Month(wsMonth.Cells(3, 2)) + 1

For j = 3 To lastRow
abbrevName = Abbrev2(wsMonth.Cells(j, 1))
Set wsProp = wbProp.Sheets(abbrevName)
wsMonth.Range(wsMonth.Cells(j, 3), wsMonth.Cells(j, 14)).Copy
wsProp.Range(wsProp.Cells(3, col), wsProp.Cells(14, col)) _
.PasteSpecial Transpose:=True
Set wsProp = Nothing
Next j

wbMonth.Close SaveChanges:=False 'Close the workbook
Set wsMonth = Nothing
Set wbMonth = Nothing
Next i



I am assuming the culprit is the "PasteSpecial". I could not figure out how to do this another way. The problem is that the input data is in a single worksheet for each month. Usually I am picking up only one month's data. The output data is a workbook for a year's worth of data, but the data is contained in worksheet's for each apartment complex, with 12 columns for the various month's data. This means the input data is row oriented and together for all complexes while the output data is column oriented on individual worksheets for each complex.

This means I open each input workbook/worksheet and distribute its data to a destination workbook that contains a worksheet per complex.

Any ideas what I'm doing wrong? It would seem that switching worksheets would not be that time consuming but switching workbooks would take more CPU cycles.

[Northwolves]

Maybe an attachment is helpful.

BestRegards
Northwolves

[jwise]

I have done additional testing. Assuming real-world execution time is proportional to debugger execution time, then the problem is with the "Copy" statement and NOT the "PasteSpecial".


wsMonth.Range(wsMonth.Cells(j, 3), wsMonth.Cells(j, 14)).Copy


Since I'm only copying 12 cells, I could have used the construct:


wsProp.Cells(3, col) = wsMonth.Cells(j, 3)
... (11 additional statements)


From comments on prior coding practices, I thought the procedural approach was less efficient than the "object" approach. What am I missing?

I built two workbooks to upload but decided against it. There is a lot of extra stuff in these workbooks that obscure the problem. I know it's in the "j" loop, and now I'm reasonably sure it's the "Copy" statement. I think there must be some other technique that's more efficient.

[rory]

You could try:
wsProp.Range(wsProp.Cells(3, col), wsProp.Cells(14, col)).Value = _
Application.Transpose(wsMonth.Range(wsMonth.Cells(j, 3), wsMonth.Cells(j, 14)).Value)
or you might try explicitly setting Application.CutCopyMode = False in between Copy calls.