Filter/split data from a master sheet - Help its driving me nuts!

[JasonWild]

Hi there,

Really hoping someone can help me with this - it is driving my nuts.
I am trying to split filtered data from a master sheet, to separate spreadsheets in the same workbook.
When I use these two macros separately, they both do what I need.

The first one filters:

Sub Filter()
ActiveSheet.Range("$A:$D").AutoFilter Field:=2, Criteria1:="No"
End Sub

The second one splits the remaining data out to different tabs, based on the column,


Sub Depotsplit()
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
vcol = 1
Set ws = Sheets("Master")
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = "A1:D1"
titlerow = ws.Range(title).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
For i = 2 To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter Field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
Sheets(myarr(i) & "").Columns.AutoFit
Next
ws.AutoFilterMode = False
ws.Activate
End Sub

The issue I am having, is that when i add the filter line in to the main script above, it will only split the first unique from the column with the filtered data, but not the others. I am finding it so confusing, as I got it to work once, but cant replicate it.
For example, I want to split out football teams, IE, Man Utd, Liverpool, Arsenal, based on matches where they scored. However it is only populating the Man Utd tab.
Hope this makes sense, can anyone shine a light on how to fix this?

Appreciate you're help,

Jason

[AlphaFrog]

Please Login or Register  to view this content.

[JasonWild]

Thanks for your reply, however this is still only pulling the one unique out.
I really don't understand why it is not pulling the others out.

[Pepe Le Mokko]

Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 6)