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]

Welcome to MrExcel.

See if you can adapt the macro in Post #2 here:

http://www.mrexcel.com/forum/excel-questions/584657-create-sep-sheets-based-data-column.html

 

[Jawnne]

Thanks, Andrew for you speedy reply, just got in. I will modify and test that code.

 

[Jawnne]

The loop works great!! How do I maintain the column widths when the autofilter loop copies to the destination?

 

[Andrew Poulsom]

Try adding:

ShNew.Range("A1").PasteSpecial xlPasteColumnWidths

 

[Jawnne]

Andrew,

This is what I have thus far. The PasteSpecial does not work.

Sub APTest()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim c As Range
    Dim List As New Collection
    Dim Item As Variant
    Dim ShNew As Worksheet
    Application.ScreenUpdating = False
'   *** Change Sheet name to suit ***
    Set Sh = ActiveSheet
    Set Rng = Sh.Range("A2:A" & Sh.Range("A65536").End(xlUp).Row)
    On Error Resume Next
    For Each c In Rng
        List.Add c.Value, CStr(c.Value)
    Next c
    On Error GoTo 0
    Set Rng = Sh.Range("A1:E" & Sh.Range("A65536").End(xlUp).Row)
    For Each Item In List
        Set ShNew = Worksheets.Add
        ShNew.Name = Item
        Rng.AutoFilter Field:=1, Criteria1:=Item
        Rng.SpecialCells(xlCellTypeVisible).Copy ShNew.Range("A1") '.PasteSpecial xlColumnWidths - compile error
        'ShNew.Range("A1").PasteSpecial Paste:=xlColumnWidths 'Run-time error '1004' PasteSpecial method of Range class failed
        Rng.AutoFilter
    Next Item
    Sh.Activate
    Application.ScreenUpdating = True
End Sub

 

[Andrew Poulsom]

It's xlPasteColumnWidths not xlColumnWidths.

 

[Jawnne]

Andrew,

I could not get that to work. I have a more pressing matter from my original post, which is how to deal with the blanks in this field. The blanks represent new patients who have not be seen as of yet. So they do not have a care team or a provider. However, they do have appointments. With the help of my Care Team Assignments table I have been manually moving them to the respective teams. Another issue is the Social Workers work for -

We have three Teams here each is called a different color (Red, White, Blue)
Each Team is sub divided by into Care Teams, which consists of Nurse, Doctor, and Social Worker.
Each Social Worker works for a Team and not for one specific Care Team, so their Care Team assignments are based on the patient's Doctor's and Nurse's Care Team Assignment.

The Problem is that there is only one Social Worker per Team, and the record cannot be associated to the Care Team by using Table 2, only by associating to the patient.

Any thoughts on how I can incorporate this to the existing macro would be greatly appreciated.


Table 1: Raw Data Report

Patient Name	Clinic/Provider	Appointment Date/Time	Primary Doctor	Care Team
Flintstone,Fred	Blue Team Nurse 2	09/04/2014 07:00 AM	Dr. 1	Blue 6
Flintstone,Fred	Blue Team Dr. 1	09/04/2014 07:30 AM	Dr. 1	Blue 6
Flintstone,Wilma	Red Team Nurse 8	09/04/2014 07:00 AM	Dr. 2	Red 8
Flintstone,Wilma	Red Team Dr. 2	09/04/2014 07:30 AM	Dr. 2	Red 8
Rubble,Barney	Blue Team Nurse 6	09/04/2014 07:00 AM	Dr. 3	Blue 2
Rubble,Barney	Blue Team Dr. 3	09/04/2014 07:30 AM	Dr. 3	Blue 2
Rubble,Barney	Blue Team SW 1	09/04/2014 08:00 AM	Dr. 3	Blue 2
Rubble,Betty	White Team Nurse 4	09/04/2014 07:00 AM
Rubble,Betty	White Team Dr. 4	09/04/2014 07:30 AM
Rubble,Betty	White Team SW 2	09/04/2014 08:00 AM
White,Betty	Blue Team Nurse 2	09/04/2014 08:00 AM
White,Betty	Blue Team Dr. 1	09/04/2014 08:30 AM
White,Betty	Blue SW	09/04/2014 09:00 AM

<tbody>
</tbody>

Table 2: Care Team Assignments

Provider Name	Care Team
Nurse 2	Blue 6
Nurse 4	White 1
Nurse 6	Blue 2
Nurse 8	Red 8
Dr. 1	Blue 6
Dr. 2	Red 8
Dr. 3	Blue 2
Dr. 4	White 1
SW 1	Blue

<tbody>
</tbody>

 

[Andrew Poulsom]

I don't understand, sorry. You have two Blue Teams there.

 

[Jawnne]

The Hospital has 3 wings each wing is referred to as a Team (Red Team, White Team, and Blue Team).

Each of these Teams is subdivided into Care Teams which consists of a Nurse, Doctor, and Social Worker.

So the Red Team may have 5 Care Teams (Red Team 1, Red Team 2, Red Team 6, Red Team 8 just as an example).

There is only 1 Social Worker per wing so they are referred to by Blue SW, Red SW, and White SW.
The Social Worker does not have its own Care Team but is dependent on who the patient is assigned or in this case scheduled to see.