I have created 2 command buttons for materials usage - the first button deletes rows with cell G=0, sorts then subtotaled them; the second button - updates/refreshes the file.
After the first button is done - both buttons worked OK - as long as the view is not changed > but when the [2] at the upper left is clicked (to view the summarized data) - the first button just become dead!
hope the following images can help analyze the problem.
many thanks
the first image is the main data
the second image is the result of sort/subtotal
the sub routines are as follows:
Private Sub CommandButton1_Click() (deletes rows with G=0, sort then subtotal)
Worksheets("MU").Activate
Dim i As Long (this deletes all rows with zero values for cell G)
For i = Cells(Rows.Count, "G").End(xlUp).Row To 2 Step -1
If Cells(i, "G") = 0 Then Rows(i).Delete
Next i
Range("A2:G206").Select
ActiveWorkbook.Worksheets("MU").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("MU").Sort.SortFields.Add Key:=Range("A2:A206") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("MU").Sort
.SetRange Range("A2:G206")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1:G206").Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(7), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Dim LR As Long (this fills up the unit of measure missing in the subtotal)
LR = Range("C" & Rows.Count).End(xlUp).Offset(1).Row
Range("C2:C" & LR).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
End Sub
Private Sub CommandButton2_Click() (this copies back the data from sheet "usage" back to sheet "MU")
Worksheets("MU").Activate
Cells.Select
Selection.RemoveSubtotal
Sheets("usage").Range("A2:G555").Copy Sheets("MU").Range("A2")
End Sub
