VBA Looping AutoFilter Criteria using Dynamic Array

[Jawnne]

Hello All,


I am an old user of Excel 2003 with some knowledge of VBA, my new job uses Excel 2010 (a lot has changed since that version I see). I could use some help on how to approach this new problem.


Objective: I want to take a exported report of raw data which lets say has 5 column of pertinent information (Name, Clinic, Appointment Date/Time, Primary Doctor, and Care Team). I want to automate the process of filtering the each Care Team criteria, select all visible cells, copy it to a new worksheet, and use each criteria as the name of the new sheet.


I don't want to use Advanced Filter at this point, I would like to use the AutoFilter, Array_Filter, and a loop if necessary with some explanation of what is going on behind the code that would further my understanding that would be great.


This is what I have pieced together so far:


Sub FilterReport()


Dim sSheet As String
ActiveSheet.UsedRange.Select
Selection.Copy


sSheet = InputBox("What is the name of this Worksheet?", "Name New Worksheet")
intCount = Sheets.Count
Worksheets.Add(After:=Worksheets(intCount)).Name = sSheet
Range("A1").Select
Selection.PasteSpecial _
Paste:=xlPasteColumnWidths, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
ActiveSheet.Paste


End Sub


I found this code in one of my searches, not sure how to incorporate it if at all:


.UsedRange.AutoFilter field:=7, Criteria1:=Array_filter, Operator:=xlFilterValues


Also, in my data I have blanks as in this field which refers to patients not having a Care Team or Doctor. Which at which time I would have to look at their see what doctor they have an appointment with, reference another table to see what Care Team that Doctor is assigned to. This is usually half of the blanks which refers to a patient, the other half are those same patients which are assigned a Social Worker who works for a group of teams which the only way to know what team they are on is to associate the other matching name. All of these I want to be able to paste into the other sheets corresponding to the Care Teams previously or simultaneously created.


Thanks in advance for any help anyone can provide.

 

[Andrew Poulsom]

So what are you trying to look up and where?

 

[Jawnne]

Basically, what I do manually is to AutoFilter the Care Team field criteria to blanks, then filter the patient. Usually this will give me two to three records for each patient depending if the patient is scheduled with a Social Worker. Then I cross reference the Care Team Assignments table to find what care team that Nurse or Doctor is on and cut and paste the set of appointment to the corresponding worksheet that is set up by care team (Blue 1, Blue 2, Red 1 ... etc.). This is okay to do if it was for just a few patients, but when it is a few thousand it becomes very tedious and time consuming.

 

[Jawnne]

To Recap:
AutoFilter Care Team field Criteria:=Blanks
AutoFilter Patient field Criteria:= Each Patient
Cross Reference Clinic field in Raw Data Report with Provider in the Care Team Assignments Table to get the Care Team
** Clinic will be by Team and Provider (Blue Dr. 1) where as the Provider will just be Dr. 1 and the Care Team will be Blue #.
Then paste to appropriate sheet.