Any tricks anyone has to make this run faster?
Code:
Private Sub Consolidate_Click()
Dim LastRow As Long
Dim i As Long
Dim endall As Long
Worksheets("Wait_Display").Visible = xlSheetVisible
Worksheets("Wait_Display").Select
Application.ScreenUpdating = False
Worksheets("DB_Inv_Item_List").Select
Range("CC7:CJ5000").Select
Selection.ClearContents
Worksheets("DB_Inv_Item_List").Cells(2, 4).Value = Now()
'Catagory 100
endall = Worksheets("DB_Inv_Item_List").Range("g5").Value + 7 'Ref a count of how many cells are being actually used
For i = 7 To endall
LastRow = Worksheets("DB_Inv_Item_List").Range("cc65536").End(xlUp).Row + 1
If Sheets("DB_Inv_Item_List").Cells(i, 1).Value = "ACTIVE" Then
Worksheets("DB_Inv_Item_List").Cells(LastRow, 81).Value = Worksheets("DB_Inv_Item_List").Cells(i, 2).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 82).Value = Worksheets("DB_Inv_Item_List").Cells(i, 3).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 83).Value = Worksheets("DB_Inv_Item_List").Cells(i, 4).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 84).Value = Worksheets("DB_Inv_Item_List").Cells(i, 5).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 85).Value = Worksheets("DB_Inv_Item_List").Cells(i, 6).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 86).Value = Worksheets("DB_Inv_Item_List").Cells(i, 7).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 87).Value = Worksheets("DB_Inv_Item_List").Cells(i, 8).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 88).Value = Worksheets("DB_Inv_Item_List").Cells(i, 9).Value
Else
End If
Next i
'Catagory 200
endall = Worksheets("DB_Inv_Item_List").Range("q5").Value + 7
For i = 7 To endall
LastRow = Worksheets("DB_Inv_Item_List").Range("cc65536").End(xlUp).Row + 1
If Sheets("DB_Inv_Item_List").Cells(i, 11).Value = "ACTIVE" Then
Worksheets("DB_Inv_Item_List").Cells(LastRow, 81).Value = Worksheets("DB_Inv_Item_List").Cells(i, 12).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 82).Value = Worksheets("DB_Inv_Item_List").Cells(i, 13).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 83).Value = Worksheets("DB_Inv_Item_List").Cells(i, 14).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 84).Value = Worksheets("DB_Inv_Item_List").Cells(i, 15).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 85).Value = Worksheets("DB_Inv_Item_List").Cells(i, 16).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 86).Value = Worksheets("DB_Inv_Item_List").Cells(i, 17).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 87).Value = Worksheets("DB_Inv_Item_List").Cells(i, 18).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 88).Value = Worksheets("DB_Inv_Item_List").Cells(i, 19).Value
Else
End If
Next i
'Catagory 300
endall = Worksheets("DB_Inv_Item_List").Range("aa5").Value + 7
For i = 7 To endall
LastRow = Worksheets("DB_Inv_Item_List").Range("cc65536").End(xlUp).Row + 1
If Sheets("DB_Inv_Item_List").Cells(i, 21).Value = "ACTIVE" Then
Worksheets("DB_Inv_Item_List").Cells(LastRow, 81).Value = Worksheets("DB_Inv_Item_List").Cells(i, 22).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 82).Value = Worksheets("DB_Inv_Item_List").Cells(i, 23).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 83).Value = Worksheets("DB_Inv_Item_List").Cells(i, 24).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 84).Value = Worksheets("DB_Inv_Item_List").Cells(i, 25).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 85).Value = Worksheets("DB_Inv_Item_List").Cells(i, 26).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 86).Value = Worksheets("DB_Inv_Item_List").Cells(i, 27).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 87).Value = Worksheets("DB_Inv_Item_List").Cells(i, 28).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 88).Value = Worksheets("DB_Inv_Item_List").Cells(i, 29).Value
Else
End If
Next i
'Catagory 400
endall = Worksheets("DB_Inv_Item_List").Range("ak5").Value + 7
For i = 7 To endall
LastRow = Worksheets("DB_Inv_Item_List").Range("cc65536").End(xlUp).Row + 1
If Sheets("DB_Inv_Item_List").Cells(i, 31).Value = "ACTIVE" Then
Worksheets("DB_Inv_Item_List").Cells(LastRow, 81).Value = Worksheets("DB_Inv_Item_List").Cells(i, 32).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 82).Value = Worksheets("DB_Inv_Item_List").Cells(i, 33).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 83).Value = Worksheets("DB_Inv_Item_List").Cells(i, 34).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 84).Value = Worksheets("DB_Inv_Item_List").Cells(i, 35).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 85).Value = Worksheets("DB_Inv_Item_List").Cells(i, 36).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 86).Value = Worksheets("DB_Inv_Item_List").Cells(i, 37).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 87).Value = Worksheets("DB_Inv_Item_List").Cells(i, 38).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 88).Value = Worksheets("DB_Inv_Item_List").Cells(i, 39).Value
Else
End If
Next i
'Catagory 500
endall = Worksheets("DB_Inv_Item_List").Range("au5").Value + 7
For i = 7 To endall
LastRow = Worksheets("DB_Inv_Item_List").Range("cc65536").End(xlUp).Row + 1
If Sheets("DB_Inv_Item_List").Cells(i, 41).Value = "ACTIVE" Then
Worksheets("DB_Inv_Item_List").Cells(LastRow, 81).Value = Worksheets("DB_Inv_Item_List").Cells(i, 42).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 82).Value = Worksheets("DB_Inv_Item_List").Cells(i, 43).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 83).Value = Worksheets("DB_Inv_Item_List").Cells(i, 44).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 84).Value = Worksheets("DB_Inv_Item_List").Cells(i, 45).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 85).Value = Worksheets("DB_Inv_Item_List").Cells(i, 46).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 86).Value = Worksheets("DB_Inv_Item_List").Cells(i, 47).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 87).Value = Worksheets("DB_Inv_Item_List").Cells(i, 48).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 88).Value = Worksheets("DB_Inv_Item_List").Cells(i, 49).Value
Else
End If
Next i
'Catagory 600
endall = Worksheets("DB_Inv_Item_List").Range("be5").Value + 7
For i = 7 To endall
LastRow = Worksheets("DB_Inv_Item_List").Range("cc65536").End(xlUp).Row + 1
If Sheets("DB_Inv_Item_List").Cells(i, 51).Value = "ACTIVE" Then
Worksheets("DB_Inv_Item_List").Cells(LastRow, 81).Value = Worksheets("DB_Inv_Item_List").Cells(i, 52).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 82).Value = Worksheets("DB_Inv_Item_List").Cells(i, 53).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 83).Value = Worksheets("DB_Inv_Item_List").Cells(i, 54).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 84).Value = Worksheets("DB_Inv_Item_List").Cells(i, 55).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 85).Value = Worksheets("DB_Inv_Item_List").Cells(i, 56).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 86).Value = Worksheets("DB_Inv_Item_List").Cells(i, 57).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 87).Value = Worksheets("DB_Inv_Item_List").Cells(i, 58).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 88).Value = Worksheets("DB_Inv_Item_List").Cells(i, 59).Value
Else
End If
Next i
'Catagory 700
endall = Worksheets("DB_Inv_Item_List").Range("bo5").Value + 7
For i = 7 To endall
LastRow = Worksheets("DB_Inv_Item_List").Range("cc65536").End(xlUp).Row + 1
If Sheets("DB_Inv_Item_List").Cells(i, 61).Value = "ACTIVE" Then
Worksheets("DB_Inv_Item_List").Cells(LastRow, 81).Value = Worksheets("DB_Inv_Item_List").Cells(i, 2).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 82).Value = Worksheets("DB_Inv_Item_List").Cells(i, 3).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 83).Value = Worksheets("DB_Inv_Item_List").Cells(i, 4).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 84).Value = Worksheets("DB_Inv_Item_List").Cells(i, 5).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 85).Value = Worksheets("DB_Inv_Item_List").Cells(i, 6).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 86).Value = Worksheets("DB_Inv_Item_List").Cells(i, 7).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 87).Value = Worksheets("DB_Inv_Item_List").Cells(i, 8).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 88).Value = Worksheets("DB_Inv_Item_List").Cells(i, 9).Value
Else
End If
Next i
'Catagory 800
endall = Worksheets("DB_Inv_Item_List").Range("by5").Value + 7
For i = 7 To endall
LastRow = Worksheets("DB_Inv_Item_List").Range("cc65536").End(xlUp).Row + 1
If Sheets("DB_Inv_Item_List").Cells(i, 71).Value = "ACTIVE" Then
Worksheets("DB_Inv_Item_List").Cells(LastRow, 81).Value = Worksheets("DB_Inv_Item_List").Cells(i, 2).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 82).Value = Worksheets("DB_Inv_Item_List").Cells(i, 3).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 83).Value = Worksheets("DB_Inv_Item_List").Cells(i, 4).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 84).Value = Worksheets("DB_Inv_Item_List").Cells(i, 5).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 85).Value = Worksheets("DB_Inv_Item_List").Cells(i, 6).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 86).Value = Worksheets("DB_Inv_Item_List").Cells(i, 7).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 87).Value = Worksheets("DB_Inv_Item_List").Cells(i, 8).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 88).Value = Worksheets("DB_Inv_Item_List").Cells(i, 9).Value
Else
End If
Next i
ActiveWorkbook.Save
Application.ScreenUpdating = True
Sheets("DB_Inv_Item_List").Select
Worksheets("Wait_Display").Visible = xlSheetHidden
End Sub