Ideas to make this faster

[mnordeen]

Any tricks anyone has to make this run faster?

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

 

[Snakehips]

Mike,

For each row that you are checking will there only ever be one category to be found?
Ie if ACTIVE appears in column A for row 7 is that it for row 7 or might ACTIVE appear in say K as well?

 

[Snakehips]

Possibly answering my own question?....

No ? You have varying depths of data in 10 column blocks by category???

 

[mnordeen]

Thanks Tony,
They can and will be different, there are 8 separate tables. I just think I found my solution I tried some of the tips found here and it appears to have gone from 8 minutes to under 30 seconds. I am still double checking but first look these tips were the way to go.
Ten Simple Tricks to Speed up Your Excel VBA Code | Bacon Bits

 

[Snakehips]

Glad you have found helpful info.

Is it correct that the final values for category 700 and 800 revery to values from columns 2 to 9 i.e. same as cat 100 ??
Rather than continue to increase columns and be columns 62 to 69 and 72 to 79 ????

 

[mnordeen]

In Case the link were to go away at some point this is what is on the page written on DataPigs
There are some good ones in here, but be sure to read what they do as they may effect your sheet.

Halt Sheet Calculations
Each time a cell that affects any formula in your spreadsheet is changed or manipulated, Excel recalculates the entire worksheet. In worksheets that have a large amount of formulas, this behavior can drastically slow down your macros. If your workbook is formula intensive, you may not want Excel to trigger a recalculation every time a cell value is altered by your macro. You can use the Application.Calculation property to tell Excel to switch to manual calculation mode. When a workbook is in manual calculation mode, the workbook will not recalculate until you explicitly trigger a calculation. The idea is to place Excel into manual calculation mode, run your code, and then switch back to automatic calculation mode. Setting the calculation mode back to xlCalculationAutomatic will automatically trigger a recalculation of the worksheet.
Application.Calculation = xlCalculationManual

‘Place your macro code here


Application.Calculation = xlCalculationAutomatic


[h=1].
[/h]Disable Sheet Screen Updating

You may notice that when your macros run, your screen does a fair amount of flickering. This flickering is Excel trying to redraw the screen in order to show the current state the worksheet is in. Unfortunately, each time Excel redraws the screen, it takes up memory resources. In most cases, you don’t need Excel using up resources to redraw the screen each time your macro performs some action. In addition to setting the calculation mode to manual, you can use the Application.ScreenUpdating property to disable any screen updates until your macro has completed. This saves time and resources, allowing your macro to run a little faster. Once you macro code is done running, you can turn screen updating back on.
Application.Calculation = xlCalculationManual

Application.ScreenUpdating = False

‘Place your macro code here

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

[h=1].
[/h]Turn Off Status Bar Updates

The status bar normally displays the progress of certain actions in Excel. For example, if you copy/paste a range, Excel will show the progress of that operation on the status bar. Often times, the action is performed so fast, you don’t see the status bar progress. However, if your macro is working with lots of data, the status bar will take up some resources. It’s important to note that turning off screen updating is separate from turning off the status bar display. That is to say, the status bar will continue to be updated even if you disable screen updating. You can use the Application.DisplayStatusBar property to temporarily disable any status bar updates, further improving the performance of your macro.
Application.Calculation = xlCalculationManual

Application.ScreenUpdating = False

Application.DisplayStatusBar = False

‘Place your macro code here

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

Application.DisplayStatusBar = True

[h=1].
[/h]Tell Excel to Ignore Events

Let’s say you have a Worksheet_Change event implemented for Sheet1 of your workbook. Any time a cell or range is altered on Sheet1, the Worksheet_Change event will fire. So if you have a standard macro that manipulates several cells on Sheet1, each time a cell on that sheet is changed, your macro has to pause while the Worksheet_Change event runs. You can imagine how this behavior would slow down your macro. You can You can add another level of performance boosting by using EnableEvents property to tell Excel to ignore events while your macro runs. Simply set the EnableEvents property False before running your macro. Once you macro code is done running, you can set the EnableEvents property back to True.
Application.Calculation = xlCalculationManual

Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.EnableEvents = False

‘Place your macro code here

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

Application.DisplayStatusBar = True

Application.EnableEvents = True

[h=1].
[/h]Although disabling events can indeed speed up your macros, you may actually need some events to trigger while your macro runs. Be sure to think about our specific scenario and determine what will happen if your worksheet or workbook events are turned off while your macro runs.
[h=1].
[/h]Hide Page Breaks

Another opportunity for a performance boost can be found in Page Breaks. Each time your macro modifies the number of rows, modifies the number of columns, or alters the Page Setup of a worksheet, Excel will be forced to take up time recalculating where the page breaks are shown on the sheet. You can avoid this by simply hiding the page breaks before starting your macro. Set the DisplayPageBreaks sheet property to False in order to hide page breaks. If you want to continue to show page breaks after your macro runs, you can set the DisplayPageBreaks sheet property back to True.
Application.Calculation = xlCalculationManual

Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.EnableEvents = False

Activesheet.DisplayPageBreaks = False

‘Place your macro code here

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

Application.DisplayStatusBar = True

Application.EnableEvents = True

Activesheet.DisplayPageBreaks = True

[h=1].
[/h]Suspend Pivot Table Updates

If your macro manipulates pivot tables that contain large data sources, you may experience poor performance when doing things like dynamically adding or moving pivot fields. This is because each change you make to the structure of the pivot table requires Excel to recalculate all values in the pivot table for each pivot field your macro touches. You can improve the performance of your macro by suspending the recalculation of the pivot table until all your pivot field changes have been made. Simply set the PivotTable.ManualUpdate property to True to defer recalculation, run your macro code, and then set the PivotTable.ManualUpdate property back to False to trigger the recalculation.
ActiveSheet.PivotTables(“PivotTable1″).ManualUpdate=True

‘Place your macro code here

ActiveSheet.PivotTables(“PivotTable1″).ManualUpdate=False

[h=1].
[/h]Steer Clear of Copy and Paste

It’s important to remember that while the Macro Recorder saves time by writing VBA code for you, it doesn’t always write the most efficient code. A prime example of this is how the Macro Recorder captures any copy and paste action you perform while recording. If you were to copy cell A1 and paste it into cell B1 while recording a macro, the Macro Recorder would capture this:
Range(“A1″).Select

Selection.Copy

Range(“B1″).Select

ActiveSheet.Paste

While this code will indeed copy from cell A1 and paste into B1, it forces Excel to utilize the clipboard which adds a kind of middle man where there does not need to be one. You can give your macros a slight boost by cutting out the middle man and performing a direct copy from one cell to a destination cell. This alternate code uses the Destination argument to bypass the clipboard and copy the contents of cell A1 directly to cell B1.
Range(“A1?).Copy Destination:=Range(“B1?)

If you only need to copy values (not formatting or formulas), you can improve performance even more by avoiding the Copy method all together. Simply set the value of the destination cell to the same value found in the source cell. This method is about approximately 25 times faster than using the Copy method.
Range(“B1?).Value = Range(“A1?).Value

If you need to copy only the formulas from one cell to another, (not values or formatting), you can set the formula of the destination cell to the same formula contains in the source cell.
Range(“B1?).Formula = Range(“A1?).Formula

[h=1].
[/h]Use the With Statement

When recording macros, it’s not uncommon to manipulate the same object more than once. For example, your code may change the formatting of cell A1 so that it is underlined, italicized, and formatted bold. If you were to record a macro applying these formatting options to cell A1, you would get something like this.
Range(“A1″).Select

Selection.Font.Bold = True

Selection.Font.Italic = True

Selection.Font.Underline = xlUnderlineStyleSingle

Unfortunately, this code is not as efficient as it could be because it forces Excel to select and then change each property separately. You can save time and improve performance by using the With statement to perform several actions on a given object in one shot. The With statement utilized in this example tells Excel to apply all the formatting changes at one time. Getting into the habit of chunking actions into With statements will not only keep your macros running faster, but it will also help to more easily read your macro code.
With Range(“A1″).Font

.Bold = True

.Italic = True

.Underline = xlUnderlineStyleSingle

End With

[h=1].
[/h]Don’t Explicitly Select Objects

The Macro Recorder is quite fond of using the Select method to explicitly select objects before taking actions on them. If you were to record a macro while entering the value 1000 in cell A1 for multiple sheets, you would end up with code that looks similar to this.
Sheets(“Sheet1″).Select

Range(“A1″).Select

ActiveCell.FormulaR1C1 = “1000”

Sheets(“Sheet2″).Select

Range(“A1″).Select

ActiveCell.FormulaR1C1 = “1000”

Sheets(“Sheet3″).Select

Range(“A1″).Select

ActiveCell.FormulaR1C1 = “1000”

While this code will run fine, it’s not at all efficient. It forces Excel to take the time to explicitly select each object that is being manipulated. There is generally no need to select objects before working with them. In fact, you can dramatically improve macro performance by not using the Select method. Make it a habit to remove the Select method from any generated code. In this case, the optimized code would look like this. Note that the nothing is being selected. The code simply uses the object hierarchy to apply the needed actions.
Sheets(“Sheet1″).Range(“A1″).FormulaR1C1 = “1000”

Sheets(“Sheet2″).Range(“A1″).FormulaR1C1 = “1000”

Sheets(“Sheet3″).Range(“A1″).FormulaR1C1 = “1000”

[h=1].
[/h]Avoid Excessive Trips to the Worksheet
Another way to speed up your macros is to limit the amount of times you reference worksheet data in your code. It is always less efficient to grab data from the worksheet than from memory. That is to say, your macros will run much faster if they do not have to repeatedly interact with the worksheet. For instance, this simple code forces VBA to continuously return to Sheets(“Sheet1″).Range(“A1″) to get the number needed for the comparison being performed in the If statement.
For ReportMonth = 1 To 12

If Range(“A1″).Value = ReportMonth Then

MsgBox 1000000 / ReportMonth

End If

Next ReportMonth


A much more efficient way is to save the value in Sheets(“Sheet1″).Range(“A1″) into a variable. This way, the code references the variable instead of the worksheet.
Dim MyMonth as Integer

MyMonth = Range(“A1″).Value

For ReportMonth = 1 To 12

If MyMonth = ReportMonth Then

MsgBox 1000000 / ReportMonth

End If

Next ReportMonth

 

[mnordeen]

Tony
GREAT CATCH thank you so much 700 and 800 are not completed yet, so when I tested it I didnt notice they were wrong.
Thank you soo much, cheers

 

[Snakehips]

MIke,

You are welcome.

I have only looked at your code and not tested it in any way.
However, unless I have dropped a big bleary-eyed clanger, can you perhaps reduce your code in this fashion?

Worksheets("Wait_Display").Select
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With Worksheets("DB_Inv_Item_List")
.Range("CC7:CJ5000").ClearContents
.Cells(2, 4).Value = Now()


For c = 0 To 70 Step 10
endall = .Cells(5, 7).Offset(0, c).Value + 7 'Ref a count of how many cells are being actually used
For i = 7 To endall
LastRow = .Range("cc65536").End(xlUp).Row + 1
If .Cells(i, 1).Offset(0, c).Value = "ACTIVE" Then
.Cells(LastRow, 81).Value = .Cells(i, 2).Offset(0, c).Value
.Cells(LastRow, 82).Value = .Cells(i, 3).Offset(0, c).Value
.Cells(LastRow, 83).Value = .Cells(i, 4).Offset(0, c).Value
.Cells(LastRow, 84).Value = .Cells(i, 5).Offset(0, c).Value
.Cells(LastRow, 85).Value = .Cells(i, 6).Offset(0, c).Value
.Cells(LastRow, 86).Value = .Cells(i, 7).Offset(0, c).Value
.Cells(LastRow, 87).Value = .Cells(i, 8).Offset(0, c).Value
.Cells(LastRow, 88).Value = .Cells(i, 9).Offset(0, c).Value
Else
End If
Next i
Next c
ActiveWorkbook.Save
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
.Select
End With


Worksheets("Wait_Display").Visible = xlSheetHidden
End Sub

If it aint right then in principle I don't think it's far off.

 

[Snakehips]

Or even...

Private Sub Consolidate_Click()
Dim LastRow As Long
Dim i As Long
Dim endall As Long
Dim c As Integer
Worksheets("Wait_Display").Visible = xlSheetVisible
Worksheets("Wait_Display").Select
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With Worksheets("DB_Inv_Item_List")
.Range("CC7:CJ5000").ClearContents
.Cells(2, 4).Value = Now()


For c = 0 To 70 Step 10
endall = .Cells(5, 7).Offset(0, c).Value + 7 'Ref a count of how many cells are being actually used
For i = 7 To endall
LastRow = .Range("cc65536").End(xlUp).Row + 1
If .Cells(i, 1).Offset(0, c).Value = "ACTIVE" Then
.Range(.Cells(LastRow, 81), .Cells(LastRow, 88)).Value = .Range(.Cells(i, 2).Offset(0, c), Cells(i, 9).Offset(0, c)).Value
Else
End If
Next i
Next c
ActiveWorkbook.Save
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
.Select
End With


Worksheets("Wait_Display").Visible = xlSheetHidden
End Sub

Please forgive if this is embarrassingly badly misinterpreted.

 

[mnordeen]

Ill try them later tonight, they both use methods I am not familiar with so I will try on a test version just in case.
Thank you again Tony