RonMcK
08-08-2008, 10:14 AM
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.
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.