VBA Error - 1004 Method of Range Class Failed

[clappouk]

Afternoon All,

I am having problems using autofilter within VBA, I am getting the above error when I used the following code:

Sub GetIP()
 
Dim DFWB As Workbook  'TM Tool
Dim NewWb As Workbook 'New Data sheet for all linked Ip addresess
Dim DataWb As Workbook 'TM Monitoring Spreadsheet
Dim DataWbs As Worksheet
Dim rtable As Range
Dim Flist As Worksheet
Dim NewWbs As Worksheet
Dim wkb As Workbook
Dim Tb As TextBox
Dim i As Integer
 
Set DFWB = ThisWorkbook
Set NewWb = Workbooks.Add(template:=xlWBATWorksheet)
' you must change the LookIn line of code to the Folder you wish to search
Set fs = Application.FileSearch
With fs
        .LookIn = "[URL="file://\\irf00969\Rst"]\\irf00969\Rst[/URL] Bootle\RST TM\VAT TM SWORD TEMPLATES\VAT TM Alerts From 13082013"
        .FileType = msoFileTypeExcelWorkbooks
        If .Execute > 0 Then
        For i = 1 To .FoundFiles.Count
Set DataWb = Workbooks.Open(.FoundFiles(i))
        
'Set up data capture sheet - initial
Set NewWbs = NewWb.Worksheets(1)
Lastrow = NewWbs.Cells(Rows.Count, 2).End(xlUp).Row
Headln = Lastrow + 2
Nextrow = Headln + 1

'Open TM Macro Sheet & get Value for DR
DFWB.Activate
DR = "212.137.36.228"

'Go back to Datasheet to get look for data to copy
DataWb.Activate 'Activate Workbook
Set DataWbs = Worksheets(1) ' Assign variable to Sheet1
DataWbs.Activate
Col = DataWbs.Application.Match("IPADDR", Rows("6:6"), 0)
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False [COLOR=#008000]' if autofilter is on switch off[/COLOR]
[COLOR=#ff0000]DataWbs.Range("A6:CW6").AutoFilter Field:=Col, Criteria1:=DR, Operator:= _
        xlAnd[/COLOR]
        
Set rtable = DataWbs.Range("A6").CurrentRegion

The macro works fine upto where I want to apply the autofilter (get the above Error) - could the problem be its 100 columns wide?

Col = Supplies the Field Ref by using match to find which column "IPADDR" is in
DR = is the IP Address I am looking for within the spreadsheet

rtable = all the cells provided by the working autofilter



Any help will be apprciated

Ray Excel 2003