I have a VBA Sub that copies data from one worksheet to another, transposing some of the columns (which have headers indicated the Quarter and number of units sold per product for that quarter) so that on the new sheet there's a column called "Quarter" and one called "Units". The problem is, it's running quite slowly - there are 25,000 rows in the source worksheet and after 24 hours it had only processed 16,000 rows. Since it will have to process all 25,000 rows 12 times to get all the quarters, it obviously needs to run faster. This is in Excel 2007. Here's the code, any feedback is appreciated:
Code:
Sub transposeData3()
'variables to locate rows and columns
Dim LastRow As Double, LastCol As Double, i As Double, q As Double, z As Double
'variables to refer to worksheets
Dim SourceSheet As Worksheet, ResultSheet As Worksheet
Set SourceSheet = ActiveWorkbook.Worksheets("iMANY FFS (per state per ndc11)")
Set ResultSheet = ActiveWorkbook.Worksheets("Sheet3")
LastRow = SourceSheet.Range("A65536").End(xlUp).Row
LastCol = SourceSheet.Range("Z1").End(xlToLeft).Column
z = 2
For q = 6 To LastCol
For i = 2 To LastRow - 1
ResultSheet.Range("A" & z).Value = SourceSheet.Range("A" & i).Value
ResultSheet.Range("B" & z).Value = SourceSheet.Range("B" & i).Value
ResultSheet.Range("C" & z).Value = SourceSheet.Range("D" & i).Value
ResultSheet.Range("D" & z).Value = SourceSheet.Cells(1, q).Value
ResultSheet.Range("E" & z).Value = SourceSheet.Cells(i, q).Value
z = z + 1
Next
Next
End Sub
ffice
></o
