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