Sub TestSort()
Dim LastRow As Long, LastRow1 As Long, LastRow2 As Long
Dim LastCol As Long, LastCol1 As Long, LastCol2 As Long
Dim S As String, t As String, u As String
Dim h As Long, i As Long
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.DisplayAlerts = False
LastRow = FindLastRow(Sheets("Overview").Cells)
LastCol = FindLastcolumn(Sheets("Overview").Cells)
S = ConvertToLetter(LastCol)
If Range("C1") = "Patient ID" Then
srt_Patient_ID
ElseIf Range("C1") = "Registry ID" Then
srt_Registry_ID
ElseIf Range("C1") = "Patient Initials" Then
srt_Patient_Initials
ElseIf Range("C1") = "Date Of Birth" Then
srt_Date_Of_Birth
ElseIf Range("C1") = "Sex" Then
srt_Sex
ElseIf Range("C1") = "Informed Consent" Then
srt_Informed_Consent
ElseIf Range("C1") = "Center" Then
srt_Center
ElseIf Range("C1") = "Date ABPM Inclusion" Then
srt_Date_ABPM_Inclusion
ElseIf Range("C1") = "Base Completed" Then
srt_Base_Completed
ElseIf Range("C1") = "Medical History Completed" Then
srt_Medical_History_Completed
ElseIf Range("C1") = "Medication Completed" Then
srt_Medication_Completed
ElseIf Range("C1") = "Office BP Completed" Then
srt_Office_BP_Completed
ElseIf Range("C1") = "ABPM Completed" Then
srt_ABPM_Completed
ElseIf Range("C1") = "Laboratory Results Completed" Then
srt_Laboratory_Results_Completed
ElseIf Range("C1") = "Follow Up Completed" Then
srt_Follow_Up_Completed
End If
srt_Overview_Included_in_Registry
i = 1
For h = 4 To LastRow
Range(S & h) = i
i = i + 1
Next h
Sheets("Base").Select
LastRow1 = FindLastRow(Sheets("Base").Cells)
LastCol1 = FindLastcolumn(Sheets("Base").Cells)
t = ConvertToLetter(LastCol1)
For h = 4 To LastRow
For i = 4 To LastRow1
If Sheets("Overview").Range("A" & h) = Sheets("Base").Range("A" & i) Then
Sheets("Base").Range(t & i) = Sheets("Overview").Range(S & h)
End If
Next i
Next h
srt_Base_Index
Sheets("Medication").Select
LastRow2 = FindLastRow(Sheets("Medication").Cells)
LastCol2 = FindLastcolumn(Sheets("Medication").Cells)
u = ConvertToLetter(LastCol2)
For h = 4 To LastRow
For i = 4 To LastRow2
If Sheets("Overview").Range("A" & h) = Sheets("Medication").Range("A" & i) Then
Sheets("Medication").Range(u & i) = Sheets("Overview").Range(S & h)
End If
Next i
Next h
srt_Medication_Index
Sheets("Overview").Select
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub
Sub srt_Patient_ID()
Dim ws As Worksheet
Dim rng As Range
Dim tbl As ListObject
Dim sortcolumn As Range
Set ws = Sheets("Overview")
Set tbl = ws.ListObjects("Table1")
Set sortcolumn = Range("Table1[Patient ID]")
With tbl.Sort
.SortFields.Clear
.SortFields.Add Key:=sortcolumn, SortOn:=xlSortOnValues, Order:=xlAscending
.Header = xlYes
.Apply
End With
End Sub
Sub srt_Registry_ID()
Dim ws As Worksheet
Dim rng As Range
Dim tbl As ListObject
Dim sortcolumn As Range
Set ws = Sheets("Overview")
Set tbl = ws.ListObjects("Table1")
Set sortcolumn = Range("Table1[Registry ID]")
With tbl.Sort
.SortFields.Clear
.SortFields.Add Key:=sortcolumn, SortOn:=xlSortOnValues, Order:=xlAscending
.Header = xlYes
.Apply
End With
End Sub
Sub srt_Patient_Initials()
Dim ws As Worksheet
Dim rng As Range
Dim tbl As ListObject
Dim sortcolumn As Range
Set ws = Sheets("Overview")
Set tbl = ws.ListObjects("Table1")
Set sortcolumn = Range("Table1[Patient Initials]")
With tbl.Sort
.SortFields.Clear
.SortFields.Add Key:=sortcolumn, SortOn:=xlSortOnValues, Order:=xlAscending
.Header = xlYes
.Apply
End With
End Sub
Sub srt_Date_Of_Birth()
Dim ws As Worksheet
Dim rng As Range
Dim tbl As ListObject
Dim sortcolumn As Range
Set ws = Sheets("Overview")
Set tbl = ws.ListObjects("Table1")
Set sortcolumn = Range("Table1[Date Of Birth]")
With tbl.Sort
.SortFields.Clear
.SortFields.Add Key:=sortcolumn, SortOn:=xlSortOnValues, Order:=xlAscending
.Header = xlYes
.Apply
End With
End Sub
Sub srt_Sex()
Dim ws As Worksheet
Dim rng As Range
Dim tbl As ListObject
Dim sortcolumn As Range
Set ws = Sheets("Overview")
Set tbl = ws.ListObjects("Table1")
Set sortcolumn = Range("Table1[Sex]")
With tbl.Sort
.SortFields.Clear
.SortFields.Add Key:=sortcolumn, SortOn:=xlSortOnValues, Order:=xlAscending
.Header = xlYes
.Apply
End With
End Sub
Sub srt_Informed_Consent()
Dim ws As Worksheet
Dim rng As Range
Dim tbl As ListObject
Dim sortcolumn As Range
Set ws = Sheets("Overview")
Set tbl = ws.ListObjects("Table1")
Set sortcolumn = Range("Table1[Informed Consent]")
With tbl.Sort
.SortFields.Clear
.SortFields.Add Key:=sortcolumn, SortOn:=xlSortOnValues, Order:=xlAscending
.Header = xlYes
.Apply
End With
End Sub
Sub srt_Center()
Dim ws As Worksheet
Dim rng As Range
Dim tbl As ListObject
Dim sortcolumn As Range
Set ws = Sheets("Overview")
Set tbl = ws.ListObjects("Table1")
Set sortcolumn = Range("Table1[Center]")
With tbl.Sort
.SortFields.Clear
.SortFields.Add Key:=sortcolumn, SortOn:=xlSortOnValues, Order:=xlAscending
.Header = xlYes
.Apply
End With
End Sub
Sub srt_Date_ABPM_Inclusion()
Dim ws As Worksheet
Dim rng As Range
Dim tbl As ListObject
Dim sortcolumn As Range
Set ws = Sheets("Overview")
Set tbl = ws.ListObjects("Table1")
Set sortcolumn = Range("Table1[Date ABPM Inclusion]")
With tbl.Sort
.SortFields.Clear
.SortFields.Add Key:=sortcolumn, SortOn:=xlSortOnValues, Order:=xlDescending
.Header = xlYes
.Apply
End With
End Sub
Sub srt_Base_Completed()
Dim ws As Worksheet
Dim rng As Range
Dim tbl As ListObject
Dim sortcolumn As Range
Set ws = Sheets("Overview")
Set tbl = ws.ListObjects("Table1")
Set sortcolumn = Range("Table1[Base Completed]")
With tbl.Sort
.SortFields.Clear
.SortFields.Add Key:=sortcolumn, SortOn:=xlSortOnValues, Order:=xlDescending
.Header = xlYes
.Apply
End With
End Sub
Sub srt_Medical_History_Completed()
Dim ws As Worksheet
Dim rng As Range
Dim tbl As ListObject
Dim sortcolumn As Range
Set ws = Sheets("Overview")
Set tbl = ws.ListObjects("Table1")
Set sortcolumn = Range("Table1[Medical History Completed]")
With tbl.Sort
.SortFields.Clear
.SortFields.Add Key:=sortcolumn, SortOn:=xlSortOnValues, Order:=xlDescending
.Header = xlYes
.Apply
End With
End Sub
Sub srt_Medication_Completed()
Dim ws As Worksheet
Dim rng As Range
Dim tbl As ListObject
Dim sortcolumn As Range
Set ws = Sheets("Overview")
Set tbl = ws.ListObjects("Table1")
Set sortcolumn = Range("Table1[Medication Completed]")
With tbl.Sort
.SortFields.Clear
.SortFields.Add Key:=sortcolumn, SortOn:=xlSortOnValues, Order:=xlDescending
.Header = xlYes
.Apply
End With
End Sub
Sub srt_Office_BP_Completed()
Dim ws As Worksheet
Dim rng As Range
Dim tbl As ListObject
Dim sortcolumn As Range
Set ws = Sheets("Overview")
Set tbl = ws.ListObjects("Table1")
Set sortcolumn = Range("Table1[Office BP Completed]")
With tbl.Sort
.SortFields.Clear
.SortFields.Add Key:=sortcolumn, SortOn:=xlSortOnValues, Order:=xlDescending
.Header = xlYes
.Apply
End With
End Sub
Sub srt_ABPM_Completed()
Dim ws As Worksheet
Dim rng As Range
Dim tbl As ListObject
Dim sortcolumn As Range
Set ws = Sheets("Overview")
Set tbl = ws.ListObjects("Table1")
Set sortcolumn = Range("Table1[ABPM Completed]")
With tbl.Sort
.SortFields.Clear
.SortFields.Add Key:=sortcolumn, SortOn:=xlSortOnValues, Order:=xlDescending
.Header = xlYes
.Apply
End With
End Sub
Sub srt_Laboratory_Results_Completed()
Dim ws As Worksheet
Dim rng As Range
Dim tbl As ListObject
Dim sortcolumn As Range
Set ws = Sheets("Overview")
Set tbl = ws.ListObjects("Table1")
Set sortcolumn = Range("Table1[Laboratory Results Completed]")
With tbl.Sort
.SortFields.Clear
.SortFields.Add Key:=sortcolumn, SortOn:=xlSortOnValues, Order:=xlDescending
.Header = xlYes
.Apply
End With
End Sub
Sub srt_Follow_Up_Completed()
Dim ws As Worksheet
Dim rng As Range
Dim tbl As ListObject
Dim sortcolumn As Range
Set ws = Sheets("Overview")
Set tbl = ws.ListObjects("Table1")
Set sortcolumn = Range("Table1[Follow Up Completed]")
With tbl.Sort
.SortFields.Clear
.SortFields.Add Key:=sortcolumn, SortOn:=xlSortOnValues, Order:=xlDescending
.Header = xlYes
.Apply
End With
End Sub
Sub srt_Overview_Included_in_Registry()
Dim ws As Worksheet
Dim rng As Range
Dim tbl As ListObject
Dim sortcolumn As Range
Set ws = Sheets("Overview")
Set tbl = ws.ListObjects("Table1")
Set sortcolumn = Range("Table1[Included in Registry]")
With tbl.Sort
.SortFields.Clear
.SortFields.Add Key:=sortcolumn, SortOn:=xlSortOnValues, Order:=xlDescending
.Header = xlYes
.Apply
End With
End Sub
Sub srt_Base_Index()
Dim ws As Worksheet
Dim rng As Range
Dim tbl As ListObject
Dim sortcolumn As Range
Set ws = Sheets("Base")
Set tbl = ws.ListObjects("Table2")
Set sortcolumn = Range("Table2[Index]")
With tbl.Sort
.SortFields.Clear
.SortFields.Add Key:=sortcolumn, SortOn:=xlSortOnValues, Order:=xlAscending
.Header = xlYes
.Apply
End With
End Sub
Sub srt_Medication_Index()
Dim ws As Worksheet
Dim rng As Range
Dim tbl As ListObject
Dim sortcolumn As Range
Set ws = Sheets("Medication")
Set tbl = ws.ListObjects("Table3")
Set sortcolumn = Range("Table3[Index]")
With tbl.Sort
.SortFields.Clear
.SortFields.Add Key:=sortcolumn, SortOn:=xlSortOnValues, Order:=xlAscending
.Header = xlYes
.Apply
End With
End Sub
