I've already spotted a glaring error in this sub. I'd still really appreciate some help please. Thanks!
Private Sub cmdSORHistory_Click()
' Run formatting on History1 and History2 if they contain data
'Dim i As Long
For i = 1 To 2
Set ws = Worksheets("SOR_History" & i)
Next
Dim M As Double
M = ws.Rows.Count ' avoid overflow
M = M * ws.Columns.Count
If Application.WorksheetFunction.CountBlank(ws.Cells) = M Then
MsgBox ws.name & " is empty"
' Exit Sub
End If
' Delete any row containing the words 'Z INFORMATION SHARING' in column D
'Dim i As Long
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If InStr(Cells(i, 4), "Z INFORMATION SHARING") Then
'If InStr(Cells(i, 4), "Z INFORMATION SHARING") Or InStr(Cells(i, 4), "Abcdef") Then
Rows(i).Delete
End If
Next
' Perform the basic editing
' Delete first column
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
' Find and remove all instances of ' [O]'
Columns("B:B").Select
Selection.Replace What:=" [O]", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
' Format date column
Range("$D:$D").NumberFormat = "dd/mm/yyyy"
' Delete columns and rows not required
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=-1
Rows("1:3").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
' Delete all rows with a date older than eighteen months
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
Dim FilterRange As Range, myDate As Date
myDate = DateSerial(Year(Date) - 1, Month(Date) - 6, Day(Date))
Set FilterRange = _
Range("D8:D" & Cells(Rows.Count, 1).End(xlUp).Row)
FilterRange.AutoFilter Field:=1, Criteria1:="<" & CDbl(myDate)
On Error Resume Next
With FilterRange
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).EntireRow.Delete
End With
Err.Clear
Set FilterRange = Nothing
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True
' Add row to top of worksheet
Range("$A$1").EntireRow.Insert
' Add line of text
ActiveSheet.Range("A1").Value = "This is the history shown for the past eighteen months :-" & vbCrLf
'End If
'Next ws
' Message box to show formatting SORHistory complete
Dim answer As Integer
answer = MsgBox("Formatting of SOR History Complete", vbInformation + vbOKOnly, "Triage Hub")
Application.ScreenUpdating = True
End Sub