Advanced Filter works in recorder but not while running macro
 

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
Range("O1").Select
ActiveSheet.Paste

Range("l6").Select
filtcnt2 = Selection.Value
ActiveSheet.ShowAllData




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





Range("l5").Select
unqtickcnt = Selection.Value
Range("o2").Select
ActiveCell.Range("A1:A" & unqtickcnt - 1).Select
Selection.Copy
Sheets("OFFER").Select

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

'Sort ticker list
Range("N14").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("N15"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

Range("A15").Select

'Formating
Columns("A:L").EntireColumn.AutoFit
Range("A15").Select
Application.CutCopyMode = False

Advanced Filter works in recorder but not while running macro
 

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
Range("O1").Select
ActiveSheet.Paste

Range("l6").Select
filtcnt2 = Selection.Value
ActiveSheet.ShowAllData




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

Advanced Filter works in recorder but not while running macro
 

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
* * Range("O1").Select
* * ActiveSheet.Paste

* * Range("l6").Select
* * filtcnt2 = Selection.Value
* * ActiveSheet.ShowAllData

* * Range("O1:O5").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range("P1" _
* * * * ), Unique:=True- Hide quoted text -

- Show quoted text -

Regarding Questions:
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

1. No
2. No error - I took the resume on error statements out and
tested...and there was no error
3. There is data in column 0
4. I tried your code just for kicks and it didn't work.

Any other ideas?

Advanced Filter works in recorder but not while running macro
 

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