VBA Create Sheets Excel 2013 Issue

[hobbiton73]

Hi, I wonder whether someone may be able to help me please.

I'm using the code below to create sheets with the sheet name derived from a list of names. In addition, the code copies pertinent data from the 'Source' sheet to the relevant 'Destination' sheet.

Sub CreateSheets()

    Dim WBO As Workbook
    Dim ThisWS
    Dim rngFilter As Range 'filter range
    Dim rngUniques As Range 'Unique Range
    Dim cell As Range
    Dim counter As Integer
    Dim rngResults As Range 'filter range
    Dim LastRow As Long
    Dim Values As Range
    Dim iX As Integer
    
    Set WBO = ThisWorkbook
    Set rngFilter = Range("O4", Range("O" & Rows.Count).End(xlUp))
    Set rngResults = Range("A1", Range("N" & Rows.Count).End(xlUp))
    
    With rngFilter
        .AdvancedFilter Action:=xlFilterInPlace, Unique:=True
        Set rngUniques = Range("O5", Range("O" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
    
    End With
    
    For Each cell In rngUniques
        Worksheets.Add after:=Worksheets(Worksheets.Count)
            ThisWS = cell.Value
                ActiveSheet.Name = ThisWS
                    'counter = counter + 1
                    rngFilter.AutoFilter Field:=1, Criteria1:=cell.Value
                    rngResults.SpecialCells(xlCellTypeVisible).Copy Destination:=WBO.Sheets(ThisWS).Range("A1")
                    cell.Value = 100 - cell.Value
                LastRow = Cells(Rows.Count, "B").End(xlUp).Row
                    If LastRow >= StartRow Then
                        With Range("B5:O" & LastRow)
                            .Sort Key1:=.Cells(1, 1), Order1:=xlAscending, Key2:=.Cells(1, 2), order2:=xlAscending
                        End With
                    End If
                Columns("B:O").AutoFit
           Next cell
End Sub

I've been using this code in Excel 2003 at work, but when I've tried to run this through version 2013 despite saving the file as a '.xls', I receive the following error: 'Run time error '1004': We couldn't do this for the selected range of cells. Select a single cell within a given range of data then try again.' Debug then highlights this line as the cause:

.AdvancedFilter Action:=xlFilterInPlace, Unique:=True

Could someone perhaps tell me where I'm going wrong and how I may overcome this.

Many thanks and kind regards

[AlphaFrog]

I'm not sure what the problem is. I don't have the 2013 version.
As a guess, try resetting any filtered rows before using the advanced filter. There may be a conflict if Autofilter was left on or something. It couldn't hurt.

    Set WBO = ThisWorkbook
    If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
    Set rngFilter = Range("O4", Range("O" & Rows.Count).End(xlUp))
    Set rngResults = Range("A1", Range("N" & Rows.Count).End(xlUp))
    
    With rngFilter
        .AdvancedFilter Action:=xlFilterInPlace, Unique:=True
        Set rngUniques = Range("O5", Range("O" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
    End With

[hobbiton73]

Hi @AlphaFrog, thank you for taking the time to reply to my post and for the putting the proposed solution together.

Unfortunately, I'm still receiving the same error message, but rather than me continue to work on this at home, when I go back to work tomorrow, I'll check just to make sure I'm not missing anything else from the script. I will of course let you know how I get on.

All the best and kind regards