Hello
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..
Code
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
Display More
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?
Thank You
Terry