excel macro to split data into multiple worksheets based on conditions worksheet

[Nite0wls]

I have a master data file with telecom call information. The file contains 288K rows out of that I try to filter the information for 123 numbers to collate a cost overview.

Data File - (MasterData)
[TABLE="width: 431"]
<colgroup><col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;"><colgroup><col width="57" style="width: 43pt; mso-width-source: userset; mso-width-alt: 2084;"><colgroup><col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;" span="2"><colgroup><col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;"><colgroup><col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"><colgroup><col width="60" style="width: 45pt; mso-width-source: userset; mso-width-alt: 2194;"><colgroup><col width="54" style="width: 41pt; mso-width-source: userset; mso-width-alt: 1974;"><tbody>[TR]
[TD="width: 82, bgcolor: transparent"]Date[/TD]
[TD="width: 57, bgcolor: transparent"]Time[/TD]
[TD="width: 84, bgcolor: transparent"]From[/TD]
[TD="width: 84, bgcolor: transparent"]To[/TD]
[TD="width: 90, bgcolor: transparent"]Alt To
[/TD]
[TD="width: 61, bgcolor: transparent"]Duration[/TD]
[TD="width: 60, bgcolor: transparent"]Call Cost[/TD]
[TD="width: 54, bgcolor: transparent"]Call Vat[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]31/07/2019[/TD]
[TD="bgcolor: transparent, align: right"]17:49:14[/TD]
[TD="bgcolor: transparent"]02890267625[/TD]
[TD="bgcolor: transparent"]01217125812[/TD]
[TD="bgcolor: transparent"]0121 712 5812
[/TD]
[TD="bgcolor: transparent, align: right"]887[/TD]
[TD="bgcolor: transparent, align: right"]0.1035[/TD]
[TD="bgcolor: transparent, align: right"]0.0207[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]31/07/2019[/TD]
[TD="bgcolor: transparent, align: right"]17:49:18[/TD]
[TD="bgcolor: transparent"]02890267625[/TD]
[TD="bgcolor: transparent"]01752766140[/TD]
[TD="bgcolor: transparent"]0175 276 6140[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]0.0004[/TD]
[TD="bgcolor: transparent, align: right"]0.0001[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]31/07/2019[/TD]
[TD="bgcolor: transparent, align: right"]17:49:23[/TD]
[TD="bgcolor: transparent"]02890267625[/TD]
[TD="bgcolor: transparent"]01425623109[/TD]
[TD="bgcolor: transparent"]0142 562 3109[/TD]
[TD="bgcolor: transparent, align: right"]176[/TD]
[TD="bgcolor: transparent, align: right"]0.0206[/TD]
[TD="bgcolor: transparent, align: right"]0.0041[/TD]
[/TR]
</tbody>[/TABLE]

A condition file used to filter (SplitCode)
[TABLE="width: 68"]
<colgroup><col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;"><tbody>[TR]
[TD="width: 90, bgcolor: transparent"]Number[/TD]
[/TR]
[TR]
[TD="bgcolor: #00B0F0"]01217125810[/TD]
[/TR]
[TR]
[TD="bgcolor: #00B0F0"]01217125811[/TD]
[/TR]
[TR]
[TD="bgcolor: #00B0F0"]01217125812
[/TD]
[/TR]
</tbody>[/TABLE]


I've used the following Macro:

Sub SplitandFilterSheet()
    
    'Step 1 - Name your ranges and Copy sheet
    'Step 2 - Filter by Department and delete rows not applicable
    'Step 3 - Loop until the end of the list
    
    Dim Splitcode As Range
    Sheets("Master").Select
    Set Splitcode = Range("AltSplitCode")

    For Each cell In Splitcode
        Sheets("Master").Copy After:=Worksheets(Sheets.Count)
        ActiveSheet.Name = cell.Value
        With ActiveWorkbook.Sheets(cell.Value).Range("MasterData")
            .AutoFilter Field:=5, Criteria1:="NOT EQUAL TO" & cell.Value, Operator:=xlFilterValues
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With
        ActiveSheet.AutoFilter.ShowAllData
    Next cell
    
End Sub

The step for the AutoFilter does not select the data that is not equal to the first cell in the SplitCode list
The error occurring is 'Cannot use that command on overlapping selections.'

The numbers are stored as text in both the SplitCode list and the MasterData list.

I try to create for each of the 123 numbers a separate work sheet to do analysis.

Any ideas how I can automate the creation of the worksheets?
I realise that excel might not be the best tool to manipulate 288K rows of data but unfortunately I have no other tools available.

Thanks for any insight in this.

Nite0wls

 

[Fluff]

Firstly this line

.AutoFilter Field:=5, Criteria1:="NOT EQUAL TO" & cell.Value, Operator:=xlFilterValues

should be

.AutoFilter Field:=5, Criteria1:="<>" & cell.Value

Secondly what range is covered by "MasterData" and are there any merged cells on that sheet?

 

[Nite0wls]

Master data contains all rows and columns including the header information on the sheet. I will test the “<>” but from past I recall not to use that and the text filter, when doing this manually, actually states NOT EQUAL TO. Nevertheless I will give that a go

Thanks for your feedback.

 

[Nite0wls]

The Master data contains 287507 rows, 1st rows has the headers and is included
There are 15 columns (A - P) in the list

I tried the "<>" without any luck, I got the same error.
The original file is an imported .csv and does not contain any merged cells, I cross checked.

Your question triggered however me to uncheck the "freeze top row" on the MasterData. This made the difference and the Macro now works

Thanks for your response as it helped me to resolve the issue

 

[Fluff]

Glad you sorted it & thanks for the feedback