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).AutoFil ter
End If

Workbooks(datafile).Sheets(dSheet).Cells(27, 1).AutoFilter Field:=27,
Criteria1:="=1"
Workbooks(datafile).Sheets(dSheet).UsedRange.Speci alCells(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

AutoFilter in External Workbook?
 

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).AutoFil ter
End If

Workbooks(datafile).Sheets(dSheet).Cells(27, 1).AutoFilter Field:=27,
Criteria1:="=1"
Workbooks(datafile).Sheets(dSheet).UsedRange.Speci alCells(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

AutoFilter in External Workbook?
 

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

AutoFilter in External Workbook?
 

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

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

AutoFilter in External Workbook?
 

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"

AutoFilter in External Workbook?
 

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?

AutoFilter in External Workbook?
 

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?

AutoFilter in External Workbook?
 

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