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)

Date	Time	From	To	Alt To	Duration	Call Cost	Call Vat
31/07/2019	17:49:14	02890267625	01217125812	0121 712 5812	887	0.1035	0.0207
31/07/2019	17:49:18	02890267625	01752766140	0175 276 6140	3	0.0004	0.0001
31/07/2019	17:49:23	02890267625	01425623109	0142 562 3109	176	0.0206	0.0041

<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>
</tbody>

A condition file used to filter (SplitCode)

Number

01217125810

01217125811

01217125812

<colgroup><col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;"><tbody>
</tbody>

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