Even after putting that in... it still took about 10 mins to come back... so i tried another way... Evaluate the value empty rightaway and it seemed to do the trick... comes back in 15-20 seconds now...
I also put in a command to clear contents in the destination worksheet before repopulating it...
Looking for your opinion... this being my first attempt and all
Sub ICCostofSales()
Dim ssht As Worksheet, dsht As Worksheet
Dim LC As Long, LR As Long, MyCol As Long, MyRow As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Set ssht = Sheets("ICCosSrc") '<-Sheet containing the source data
Set dsht = Sheets("ICCosDest") '<-Available empty sheet to put the new layout.
Sheets("ICCosDest").Activate
dsht.UsedRange.ClearContents
LC = ssht.Cells(1, Columns.Count).End(xlToLeft).Column
LR = ssht.Cells(Rows.Count, "A").End(xlUp).Row
dsht.Cells(1, 1) = "ICEntityCode"
dsht.Cells(1, 2) = "ProfitCenter"
dsht.Cells(1, 3) = "CostOfSales"
For MyCol = 2 To LC
For MyRow = 2 To LR
If IsEmpty(ssht.Cells(MyRow, MyCol)) = False Then
dsht.Cells(Rows.Count, "A").End(xlUp).Offset(1).Value = ssht.Cells(MyRow, 1)
dsht.Cells(Rows.Count, "B").End(xlUp).Offset(1).Value = ssht.Cells(1, MyCol)
If IsEmpty(ssht.Cells(MyRow, MyCol)) = True Then
dsht.Cells(Rows.Count, "C").End(xlUp).Offset(1).Value = 0
Else
dsht.Cells(Rows.Count, "C").End(xlUp).Offset(1).Value = ssht.Cells(MyRow, MyCol)
End If
End If
Next MyRow
Next MyCol
With Application
.Calculation = xlCalculationAutomatic
End With
End Sub