Run-time error 1004 autofilter method of range class failed

[SGP123]

Hi guys,

I have a problem with the following code in VBA. I'm very new to VBA, so I can't see what seems to be the problem.... The followin code gives me Run-time error 1004 autofilter method of range class failed....

Sub SplitData()
Dim ark As Worksheet
Dim arkNew As Worksheet
Dim Data As Range
Dim rfll As Range
Dim Rum As String
Set ark = ThisWorkbook.Sheets("Alle Rum")


With ark
Set Data = .Range(.Cells(1, 1), .Cells(.Rows.Count, 9).End(xlUp))
.Columns(.Columns.Count).Clear
.Range(.Cells(3, 6), .Cells(.Rows.Count, 6).End(xlUp)).AdvancedFilter Action:=xlFilterCopy, copyToRange:=.Cells(1, .Columns.Count), unique:=True


For Each rfll In .Range(.Cells(1, .Columns.Count), .Cells(.Rows.Count, .Columns.Count).End(xlUp))
Rum = rfll.Text


If WksExists(Rum) Then
Sheets(Rum).Cells.Clear
Else


Set arkNew = Sheets.Add
arkNew.Move After:=Worksheets(Worksheets.Count)
arkNew.Name = Rum
End If


Data.AutoFilter Field:=6, Criteria1:=Rum                      IT'S HERE THE ERROR IS!
Data.Copy Destination:=Worksheets(Rum).Cells(1, 1)
Next rfll
End With
ark.Columns(Columns.Count).ClearContents
Data.AutoFilter


End Sub


Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function

I really appreciate the help you can give me!

THANKS!

 

[Joe4]

Welcome to the Board!

Try verifying that your variables are returning the correct values before that line, i.e. see what these two Message Boxes return (put them just before the line that is erroring out)

MsgBox Data.Address
MsgBox Rum

 

[SGP123]

Thank you!

My variables returning the right values:
MsgBox Data.Address returns $A$1:$I$72 (that's the size of my table)
Msgbox Rum returns 308 (that's one of numbers which I would like to split into new sheets)

Sorry I am so lost - I will be gratefull for your help!

 

[Joe4]

MsgBox Data.Address returns $A$1:$I$72 (that's the size of my table)

Do you have any merged or protected cells in your range?

 

[SGP123]

Actually my data come from a query from the program MS query. It has crossed my mind, that it could be the problem of splitting the data into multiple sheets.
I got my data from that program because I have to update the data everyday.

 

[SGP123]

Now it can split the data, but the multiple sheets just copy the wohole tabel, instead of splitting the data based on one of the columns... Do you have a solution for that???

Thanks.....

 

[Joe4]

Without seeing what your data actually looks like, I don't think there is much I can do.