There are two things to do

1) Try doing the advance filtering manually. Use menu Data - Filter -
Advance Filter. click the Copy box and the Unique box. The slect your
source and desintation cells. This will indicate if there are other problems
with the workbook

2) Place my one line of code in a new workbook in a one line macro. Put
data in the cells O1:O5. The run the macro.

Hopefully this will isolate where the problem lies. I think the code is good.

"mburkett" wrote:

On Jun 17, 11:18 am, Joel wrote:
The code works in my worksheet using excel 2003.

1) Is the workbook shared?
2) Are you getting a compiler error or an excution error. What error
message does excel display
3) Line will not execute if there is no data in column O
4) If you are getting a compiler error then replace with this line

Range("O1:O5").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("P1"), _
Unique:=True

I just added line continuation characters so the line will not wrap.



"mburkett" wrote:
The following line was recorded in Excel but will not work in the
macro. Ideas?

Range("O1:O5").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range("P1" _
), Unique:=True

Thanks - Michael

Here is the whole macro (The line in question is toward the bottom):

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Sheets("Working").Delete
Sheets.Add.Name = "Working"
Sheets("Main").Select
Range("B14:E200").Select
Selection.Copy
Sheets("Working").Select

Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("E1").Select
Sheets("Main").Select

Range("H14:H200").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Working").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Sheets("Main").Select
Range("j14:k200").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Working").Select
Range("f1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Rows("1:1").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "CCM"
Range("B1").Select
ActiveCell.FormulaR1C1 = "TC"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Qty"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Type"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("f1").Select
ActiveCell.FormulaR1C1 = "PM Notes"
Range("g1").Select
ActiveCell.FormulaR1C1 = "Trading Instructions"
Columns("C:C").Select
Selection.Cut
Columns("B:B").Select
Selection.Insert Shift:=xlToRight

Range("C1:E200").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range( _
"H1"), Unique:=True

Range("L1").Select
ActiveCell.FormulaR1C1 = "=COUNTA(C[-4])"
Range("L1").Select
unqinq = Selection.Value
If unqinq 2 Then

Msg = "Inquiry has differing criteria. Filter like offers and
resubmit."
Style = vbOKOnly
Title = " Bad Criteria"
Message = MsgBox(Msg, Style, Title)
Sheets("Main").Select
Exit Sub
Else
End If

' Moves Data to offer sheet
Sheets("OFFER").Select
Range("B1:B3").Select
Selection.ClearContents
Range("A15:l66").Select
Selection.ClearContents
Range("l15:l66").Select
Range("F7:F11").Select
Selection.ClearContents

Range("B1").Select
ActiveCell.FormulaR1C1 = "=SUM(Working!C)"
Range("B2").Select

ActiveCell.FormulaR1C1 = "=UPPER(Working!RC[7])"
Range("B3").Select

ActiveCell.FormulaR1C1 = "=Working!R[-1]C[8]"

Range("B1:B3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Sheets("Working").Select
Range("L2").Select
ActiveCell.FormulaR1C1 = "=COUNTA(C[-11])"
Range("L2").Select
offercnt = Selection.Value

'Moves acct #
Range("A2").Select
ActiveCell.Range("A1:A" & offercnt - 1).Select
Selection.Copy
Sheets("OFFER").Select
Range("A15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'Moves Qty
Sheets("Working").Select
Range("b2").Select
ActiveCell.Range("A1:A" & offercnt - 1).Select
Selection.Copy
Sheets("OFFER").Select
Range("c15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'Moves PM Notes
Sheets("Working").Select
Range("f2").Select
ActiveCell.Range("A1:A" & offercnt - 1).Select
Selection.Copy
Sheets("OFFER").Select
Range("e15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'Moves PM Notes
Sheets("Working").Select
Range("g2").Select
ActiveCell.Range("A1:A" & offercnt - 1).Select
Selection.Copy
Sheets("OFFER").Select
Range("f15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'Lookup Custodial Acct &Trade Code
Range("q1").Select
offercnt = Selection.Value
Range("B15").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(LEFT(RC[-1],4),[MANAGER.XLS]Client!C1:C3,3,0)"
Range("B15").Select
Selection.Copy
ActiveCell.Range("A1:A" & offercnt).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("D15").Select
Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(LEFT(RC[-3],4),[MANAGER.XLS]Client!C1:C13,13,0)"

Range("D15").Select
Selection.Copy
ActiveCell.Range("A1:A" & offercnt).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'Write formulas for portfolio stats

Range("Q1").Select
offcnt = Selection.Value
Range("G15").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-6],Stats!C1:C7,2,0))=TRUE,
0,VLOOKUP(RC[-6],Stats!C1:C7,2,0))"
Range("H15").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-7],Stats!C1:C7,3,0))=TRUE,
0,VLOOKUP(RC[-7],Stats!C1:C7,3,0))"
Range("I15").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-8],Stats!C1:C7,4,0))=TRUE,
0,VLOOKUP(RC[-8],Stats!C1:C7,4,0))"
Range("J15").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-9],Stats!C1:C7,5,0))=TRUE,
0,VLOOKUP(RC[-9],Stats!C1:C7,5,0))"
Range("K15").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-10],Stats!C1:C7,6,0))=TRUE,
0,VLOOKUP(RC[-10],Stats!C1:C7,6,0))"
Range("L15").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-11],Stats!
C1:C7,7,0))=TRUE,"""",VLOOKUP(RC[-11],Stats!C1:C7,7,0))"
Range("M15").Select

Range("G15:L15").Select
Selection.Copy

ActiveCell.Range("A1:F" & offcnt).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone,
_
SkipBlanks:=False, Transpose:=False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone,
_
SkipBlanks:=False, Transpose:=False
Range("a15").Select

'Copy over list of ticker holdings
Sheets("OFFER").Select
Range("n15").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

'Filter out tickers from current account list
Sheets("Stats").Select
On Error Resume Next
ActiveSheet.ShowAllData
Sheets("Main").Select
Range("p9").Select
totcnt = Selection.Value
Range("b14").Select

ActiveCell.Range("a1:a" & totcnt - 1).Select
Selection.Copy
Sheets("Stats").Select

Range("N2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("l4").Select
accttickercnt = Selection.Value

Range("O2").Select
ActiveCell.FormulaR1C1 = "=""=""&RIGHT(RC[-1],4)"
Range("O2").Select
Selection.Copy

ActiveCell.Offset(0, -1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Rem Columns("O:O").Select
Rem Application.CutCopyMode = False
Rem Selection.ClearContents

Columns("J:K").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:=Range _
("N1:N" & accttickercnt), Unique:=False
Application.Goto Reference:="R1C11"
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy