Option Explicit
Scenario: Use an array of clinics, loop to create separate excel tabs for each of the elements in the array, filter by the cell value in column D, count the # in each clinic, take the count and populate another sheet
see video here download Excel file here to practice
Sub uoDoDClinics()
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, m As Integer
Dim MyArray()
MyArray = Array("AUDIOLOGY NBHC 1523", "COURAGE (WHITE) 1007", "FAM MED PCMH TEAM 1", "FEMALE SCREENING 1523", "HEARING CONSERVATION CLINIC", "IMMUNIZATION 1523", "IMMUNIZATION CLINIC FHCC", "INT MED PCMH TEAM 1", "MED. ASSESSMENT1523", "OCC HLTH CLINIC NBHC 237", "OPERATIONAL MEDICINE NBHC 237", "OPTOMETRY NBHC 1523", "OPTOMETRY NBHC 237", "PEDS PCMH TEAM 1", "PHARMACY PCMH TEAM 1 FHCC", "PHYSICAL THERAPY237", "PSYCHIATRY CLINIC FHCC", "PSYCHOLOGY (AMHU)1007", "PSYCHOLOGY 1007 OUTPATIENT", "PSYCHOLOGY CLINIC FHCC", "QQQCHCSIITESTGRTLKS", "SMART TEAM 1007", "SPECIAL PHYSICALS NBHC 1007", "STAFF MEDICINE NBHC 237", "STUDENT MEDICINE NBHC 237", "SUBSTANCE ABUSE REHAB FHCC", "TREATMENT ROOM BMC 1007", "WEEKEND MIL. SICK CALL 1007", "WELLNESS CLINIC MALE")
For m = 0 To 28 'note that there are 29 elements in the array . . . zero to 28
ThisWorkbook.Worksheets("Sheet3").Cells(m + 1, 1).Value = MyArray(m)
Next m
For i = LBound(MyArray) To UBound(MyArray)
Dim shData As Worksheet, shOutput As Worksheet
Worksheets.Add.Name = MyArray(i)
Set shData = ThisWorkbook.Worksheets("FY21")
Set shOutput = ThisWorkbook.Worksheets(MyArray(i))
shOutput.Rows(1).Value = shData.Rows(3).Value
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("A2").CurrentRegion 'this places you in A2 in FY21 tab & selects area with contiguous data
'The main code
Dim j, row As Long
row = 4 'you'll now start in row of the area that was selected as "CurrentRegion" . . .
For j = 4 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
Worksheets("Sheet3").Activate
ThisWorkbook.Worksheets("Sheet3").Cells(i + 1, 2).Value = ThisWorkbook.Worksheets(MyArray(i)).Range("X1").Value 'this takes the cell value in X1 (from sheet MyArray(i)) & populates Sheet3 . . .
End With
Next i
'Call CreateList
End Sub
Scenario: Once all the sheet tabs have been automatically created, this script runs through each worksheet (except for Sheet3,FY21,Sheet1) & takes each sheet's cell value in X1 and populates Sheet3, Column B
Option Explicit
Sub CreateList()
Dim mysheet As Worksheet
Dim xWs As Worksheet
Dim i As Long
Set mysheet = ThisWorkbook.Works
heets("Sheet3")
i = 1
For Each xWs In Application.ActiveWorkbook.Worksheets
If (xWs.Name <> "Sheet3") And (xWs.Name <> "FY21") And (xWs.Name <> "Sheet1") Then
mysheet.Cells(i, 2).Value = xWs.Range("X1").Value
i = i + 1
End If
Next
End Sub
Misc
// set the media controller for video view
simpleVideoView.setMediaController(mediaControls);
String[] title = {
“fake placeholder as title 0”,
" https://www.medical-life-skills.com/videos/androidNL/bestUsefulVideos/surplus_officeClip.mp4",
" https://www.medical-life-skills.com/videos/androidNL/bestUsefulVideos/Bao.mp4",
" https://www.medical-life-skills.com/videos/stats/oddsVsProbability.mp4"
};
int pos = getIntent().getIntExtra("key", 0);
if (pos == 1) {
simpleVideoView.setVideoURI(Uri.parse(“https://www.medical-life-skills.com/videos/androidNL/bestUsefulVideos/surplus_officeClip.mp4”));
simpleVideoView.setVideoURI(Uri.parse(title[1]));
simpleVideoView.setVideoURI(videoLink1);
// start a video
simpleVideoView.start();
} else if (pos == 2) {
simpleVideoView.setVideoURI(Uri.parse(title[2]));
// start a video
simpleVideoView.start();
} else if (pos == 3) {
simpleVideoView.setVideoURI(Uri.parse(title[3]));
// start a video
simpleVideoView.start();
}