code works on text and not on numbers

[fawnlemur]

Hi


The code copies the data on the master sheet (MasterData) and then takes the first cell on the list (Splitcode) and hides it using a filter and deletes all the other rows so only rows with that cell remain and renames the sheet as that cell.

Then goes down the list and makes a sheet for each cell on that list.

Sub SplitandFilterSheet()
Dim Splitcode As RangeSheets("Master").SelectSet Splitcode = Range("Splitcode")For Each cell In SplitcodeSheets("Master").Copy After:=Worksheets(Sheets.Count)ActiveSheet.Name = cell.ValueWith ActiveWorkbook.Sheets(cell.Value).Range("MasterData").AutoFilter Field:=6, Criteria1:="NOT EQUAL TO" & cell.Value, Operator:=xlFilterValues.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.DeleteEnd WithActiveSheet.AutoFilter.ShowAllDataNext cellEnd Sub

so on this instance it filters the 6th column to see if it matches the cell. so when I use text on the 6th column and the list it works perfectly.

but when I use numbers it doesn't work at all and highlights this code:

[COLOR=#0A0A0A][FONT=Roboto]With ActiveWorkbook.Sheets(cell.Value).Range("MasterData")[/FONT][/COLOR]

anyone know how to make it work on numbers

Thank you

 

[fawnlemur]

Re: code works on text and not on numbers, please help me fix it!!

please ignore above code, this is the correct code:

[COLOR=#0A0A0A][FONT=Roboto]Sub SplitandFilterSheet()Dim Splitcode As Range
Sheets("Master").Select
Set Splitcode = Range("Splitcode")
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:=6, Criteria1:="<>" & cell.Value, Operator:=xlFilterValues
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
ActiveSheet.AutoFilter.ShowAllData
Next cell
End Sub[/FONT][/COLOR]

 

[fawnlemur]

Re: code works on text and not on numbers, please help me fix it!!

anyone

 

[Fluff]

Re: code works on text and not on numbers, please help me fix it!!

try

With ActiveWorkbook.Sheets(CStr(Cell.Value)).Range("MasterData")

 

[fawnlemur]

Re: code works on text and not on numbers, please help me fix it!!

try

With ActiveWorkbook.Sheets(CStr(Cell.Value)).Range("MasterData")

Thanks this works

 

[Fluff]

Re: code works on text and not on numbers, please help me fix it!!

You're welcome & thanks for the feedback