My last thread worked like a charm. However, it seems management has changed their mind about how they would like to see data.
1. I have data from A to R and approx 1200 rows.
2. I sort by column P in ascending order
3. I created 7 Sheets for the main departments
4. In the P column I have sub categories of all the main departments, which must be copied/pasted to the appropriate sheet..
Sub SeparateSheetData() Dim rng As Range Dim rng2 As Range Dim ws As Worksheet lastr = ThisWorkbook.Worksheets("Alldata").Cells(Rows.Count, "p").End(xlUp).Row Set ws = ThisWorkbook.Sheets("Alldata") Set rng = ws.Range("v2:v11") Set rng2 = ws.Range("p2:p" & lastr) Worksheets("Alldata").Range("a1:r" & lastr).Sort key1:=Range("p1"), Order1:=xlAscending, Header:=xlYes For Each cll In rng.Cells.Value For Each c In rng2.Cells.Value If c = cll Then With Worksheets("Alldata").Range("A2:r" & lastr) .AutoFilter Field:=16, Criteria1:=c '& strSearch & "*" .Offset(1, 0).SpecialCells(xlCellTypeVisible).Cells.Copy Sheets("Community Services").Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False End With Worksheets("Alldata").Activate Worksheets("Alldata").ShowAllData Else: Exit For End If Next c Next cll End Sub
This works kinda sorta but as you can see, I have to hard code the sheet name.
Also, it iterates through all the values in the sheet for each value in my list.
And of course, the data needs to be appended to the last row on the appropriate sheet.
Any idea how I can make this happen?