AutoFilter in External Workbook?

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

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.

--
Regards,
Tom Ogilvy



--
Regards,
Tom Ogilvy


"DISMfish" wrote:

> 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
>
>

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

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

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

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

--
Regards,
Tom Ogilvy


"DISMfish" wrote:

> Switched the col/rows. Autofilter is still not working?
>
> ActiveWindow.Cells(8, 27).AutoFilter Field:=27, Criteria1:="=1"
>
>

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?

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.
--
Regards,
Tom Ogilvy


"DISMfish" wrote:

> 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?
>
>

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