jwise
04-08-2008, 07:44 AM
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.
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.