Sub uoDoDClinics() 'filters by matching "myarray" items to column 4 (which is column D--"Clinic Name");
Dim a As Long
Dim b As Long
Dim c As Long
Dim marks As String
Dim tally As Long
Dim numRows As Long
Dim lastrow As Long
Dim lastRow0 As Long
Dim lastRow1 As Long
Dim lastRow2 As Long
Dim n, i As Integer
Dim myarray()
myarray = Array("AUDIOLOGY NBHC 1523", "FEMALE SCREENING 1523", "INHALATION THERAPY CLINIC FHCC", "INT MED PCMH TEAM 1", "MED. ASSESSMENT1523", "OCC HLTH CLINIC NBHC 237", "OPTOMETRY NBHC 1523", "PHARMACY PCMH TEAM 1 FHCC", "PHYSICAL THERAPY237", "PSYCHIATRY CLINIC FHCC", "SOCIAL WORK CLINIC FHCC", "SUBSTANCE ABUSE REHAB FHCC", "WEEKEND MIL. SICK CALL 1007", "WELLNESS CLINIC FEMALE", "WELLNESS CLINIC MALE")
For i = LBound(myarray) To UBound(myarray)
Dim shData As Worksheet, shOutput As Worksheet
Worksheets.Add.Name = myarray(i) 'creates a new tab based on the "myarray" list items
Set shData = ThisWorkbook.Worksheets("FY15") 'worksheet name of the worksheet containing the raw data
Set shOutput = ThisWorkbook.Worksheets(myarray(i))
shOutput.Rows(1).Value = shData.Rows(3).Value 'select row 3 which contains the headers . . . & place in row 1 of new myarray(i) sheet
shOutput.Columns("A:V").AutoFit
shOutput.Range("A1").CurrentRegion.Offset(1).Clear 'this clears BOTH content & formatting
'Get the range of "Sheet1" worksheet
shData.Activate
Dim rg As Range
Set rg = shData.Range("A4").CurrentRegion 'this places you in A2 in FY19 tab & selects area with contiguous data
'The main code
Dim j, row As Long
row = 2 'you'll now start in row of the area that was selected as "CurrentRegion" . . .
For j = 2 To rg.Rows.Count
marks = rg.Cells(j, 4).Value
If marks = myarray(i) Then
'Copy using selections
shOutput.Range("A" & row).Resize(1, rg.Columns.Count).Value = rg.Rows(j).Value
'move to the next output row
row = row + 1
End If
Next j
Worksheets(myarray(i)).Activate
With ActiveSheet
numRows = .Cells(.Rows.Count, "B").End(xlUp).row
.Range("X1").Value = numRows - 1
Range("A2", Range("V2").End(xlDown)).Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlNo 'sort by column H2--after looking across from A2 to V2
End With
Next i
End Sub