Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter in External Workbook?
The data I am looking to autofilter by starts in row 8, col 27. Is
that what I have wrong? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter in External Workbook?
Switched the col/rows. Autofilter is still not working?
ActiveWindow.Cells(8, 27).AutoFilter Field:=27, Criteria1:="=1" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter in External Workbook?
Thanks anyway. I understand your logic now. Very compact!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP: Use the autofilter result on one workbook to filter the next list on another workbook | Excel Worksheet Functions | |||
Autofilter on protected workbook | Excel Discussion (Misc queries) | |||
Can I AutoFilter an entire workbook? | Excel Discussion (Misc queries) | |||
resetting the autofilter for users of a workbook | Excel Programming | |||
Add Autofilter to A1:Z1 on workbook | Excel Programming |