JBeaucaire put this code together for me and it works great. I would like to add at the end of the code to "Hide" the tab after it has run. Name of tab is "HD File". I appreciate all your help on this. Here is the code from JBeaucaire;
Option Explicit
Sub ReformatData()
Dim LR As Long, Rw As Long
With ActiveSheet
.AutoFilterMode = False
If .[A1].Value <> "Row Number" Then
MsgBox "The active sheet does not appear to be in the correct format"
Exit Sub
End If
LR = .Range("A" & .Rows.Count).End(xlUp).Row
With .Range("A2:A" & LR)
.Formula = "=IF(E2=0, IF(LEFT(B2, 8) = ""Subtotal"", """", B2), MAX($A$1:$A1)+1)"
.Value = .Value
End With
.Rows(1).AutoFilter Field:=5, Criteria1:="$0.00"
.Rows(1).AutoFilter Field:=2, Criteria1:="<>*subtotal*"
.Range("B2:B" & LR).ClearContents
.AutoFilterMode = False
.Rows(LR).Insert xlShiftDown
.Range("A" & LR + 1) = ""
.Range("B" & LR + 1) = "Total Cost"
.Range("B" & LR + 1).Font.Bold = True
End With
End Sub
Bookmarks