AutoFilter in External Workbook?

[DISMfish]

I am importing data from another workbook and I would like to know if
what I am doing is possible. First I open the workbook, then I run
this code:


Set wbBookdata = Workbooks.Open(fPath & datafile, False, True)
Workbooks(datafile).Activate

If Workbooks(datafile).Sheets(dSheet).AutoFilterMode = False Then
Workbooks(datafile).Sheets(dSheet).Rows(1).AutoFilter
End If

Workbooks(datafile).Sheets(dSheet).Cells(27, 1).AutoFilter Field:=27,
Criteria1:="=1"
Workbooks(datafile).Sheets(dSheet).UsedRange.SpecialCells(xlCellTypeVisible).Copy

Workbooks(datafile).Sheets(dSheet).AutoFilterMode = False

ThisWorkbook.Activate
Worksheets(strName).Paste


I don't get any errors, but everytime it pastes all the data. It looks
like the AutoFilter command is being ignored or performed in the wrong
workbook?

Thanks for any help,
Logan

 

[Guest]

Where is your data located?

Starting in row 1 or starting in row 27?

Workbooks(datafile).Sheets(dSheet).Cells(27, 1).AutoFilter Field:=27,
Criteria1:="=1"

Seems strange that you are refering to row 27 and Field:=27.

 

[DISMfish]

The data I am looking to autofilter by starts in row 8, col 27. Is
that what I have wrong?

 

[DISMfish]

Switched the col/rows. Autofilter is still not working?

ActiveWindow.Cells(8, 27).AutoFilter Field:=27, Criteria1:="=1"

 

[Guest]

Set wbBookdata = Workbooks.Open(fPath & datafile, False, True)
wbBookData.Activate
With wbBookData.sheets(dSheet)
.AutofilterMode = False
set rng = .Cells(8,27).currentRegion
fld = 27 - rng(1).column + 1
rng.Autofilter Field:=fld, Criteria:="=1"
rng.Copy thisworkbook.Worksheets( _
strName).Range("A1")
.AutofilterMode = False
end With
thisWorkbook.Activate

 

[DISMfish]

Tom,
I'm impressed. That's alot going on in a single With statement!

I tried your code and keep getting an error, but that's ok b/c I was
able to get my previous method to work. I had to make a selection
around the data column and filter by that selection. For some reason
autofilter didn't like me using the field offset in another workbook?

DataFilter_rng.Select
If Workbooks(datafile).Sheets(dSheet).AutoFilterMode = False Then
Selection.AutoFilter
End If
Selection.AutoFilter Field:=1, Criteria1:="=1"


Did you mean to set .Autofilter to True in the beginning of your with
statement?

 

[Guest]

No, I intended to remove any existing filters and apply a new one.
Obviously, if I had your workbooks sitting on my desk, I could refine/test
the code and make sure it works. I don't and it doesn't seem worth it to
guess.

 

[DISMfish]

Thanks anyway. I understand your logic now. Very compact!