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.
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.
