I have a worksheet that has subtotals/grand totals on it. As part of a process I am removing specific data from it AFTER the calculations and putting that data on another sheet (YTD) in the same workbook. When I do this, the subtotal's reference points are changing to the place I have moved the data to. I want the subtotal reference to remain the same as they originally were and turn to zero's if necessary as I have deleted the data from the starting sheet. I do not want the reference to move with the data.
This is what I use to move the data now.
Dim X As Long, LastRow As Long
Application.ScreenUpdating = False
For X = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1
If ActiveSheet.Cells(X, "A") = "PSPD" Or _
ActiveSheet.Cells(X, "A") = "PITD" Or _
ActiveSheet.Cells(X, "A") = "PEND" Or _
ActiveSheet.Cells(X, "A") = "PACD" Then
LastRow = Sheets("YTD").Cells(Rows.Count, "A").End(xlUp).Offset(1).Row
ActiveSheet.Cells(X, "A").EntireRow.Cut Destination:=Sheets("YTD").Range("A" & LastRow)
Any thoughts would be appreciated.