Solved: Error when I attempt to filter worksheet

[RonMcK]

I need to copy selected cells (3 out of a 16 cells per row) and paste them into a new target worksheet whose headings differ from the source worksheet. Rows are selected based on the text in column P containing either of two 'phrases'. Using List Manager, I isolated the rows I want.

The first challenge is getting VBA to do my filtering. Dimitris_c used advanced filtering (http://www.vbaexpress.com/forum/showthread.php?t=21059&highlight=filter+a+list), however, it appears that I need AutoFilter so I can use two criteria for my filter. Another challenge is that Excel Help's Autofilter definition and Example use different syntaxes:

Definition:
Syntax
expression.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)

Example:
Worksheets("Sheet1").Range("A1").AutoFilter _
field:=1, _
Criteria1:="Otis" _
VisibleDropDown:=False

My Code:
I was able to use the example's code after correcting for the missing commas:Sub FilterMyList()

' Range(SrcSheet & "!A1:Q800").autofilter(15, _
' "Play button", xlOr, "audio", False) ' does not work

Range("Grade 1!A1:Q856").AutoFilter _
Field:=15, _
Criteria1:="PLAY button", _
Operator:=xlOr, _
Criteria2:="audio", _
VisibleDropDown:=False

End Sub

Unfortunately, I get a run-time error 1004: Method 'Range' of object '_Global' failed.

[Bob Phillips]

This worked for me Ron



Sub FilterMyList()
' Range(SrcSheet & "!A1:Q800").autofilter(15, _
' "Play button", xlOr, "audio", False) ' does not work

Range("'Grade 1'!A1:Q856").AutoFilter _
Field:=15, _
Criteria1:="PLAY button", _
Operator:=xlOr, _
Criteria2:="audio", _
VisibleDropDown:=False

End Sub

[RonMcK]

Bob,

Interesting and odd. Since I'm using a Mac, here, I'll chalk it up as yet another difference from Windows. One more gotcha. <sigh>

I'll take this home and try it on my PC.

New query, once I have the filtered worksheet, how do I specify the range to copy and so I get only the visible cells in a column from the source worksheet into my target? Would I use something like:
ActiveWorkbook.Worksheets(mySheetNum).Range(SrcColumnLtr & <what # goes here?>).Copy _
Workbooks(TargetFileNo).Worksheets(TargetSheetNo).Range(TargetColumnLtr & "2")
Does '.Visible' get inserted on the source between .Range and .Copy?
Is the source range the entire range (rows) of the worsheet before filtering? (see 'what # goes here?' above)Thanks!

[Bob Phillips]

You specify the whole range, and qualify it with SpecialCells



rng.SpecialCells(xlCellTypeVisible)

[RonMcK]

Bob,

Thanks, I'll gen up the rest of the program/macro and see how it works.

More later, film at 11.

Cheers,

[RonMcK]

You specify the whole range, and qualify it with SpecialCells
rng.SpecialCells(xlCellTypeVisible)

Where I need to distinguish between worksheets in different workbooks, does the rng include only 'Range("A1:Q" & LastRow)' or can it include the parts in front of it: 'Workbooks(myWorkbook).Sheets(myWorksheet).Range("A1:Q" & LastRow)' ??

Thanks,

[Bob Phillips]

When you define rng originally you would specify the book and sheet, so when qualifying it with SpecialCells, there is no need to repeat that part of the hierarchy.