For the COUNTIF part,

change: =IF(COUNTIF(A2:A30,A2)1,"Duplicate","")
to: =IF(COUNTIF(A:A,A2)1,"Duplicate","")

The code for this could then simply be:

ActiveCell.Formula = _
"=IF(COUNTIF(A:A,A" & ActiveCell.Row & ")1,""Duplicate"","""")"

But see a better approach below in the amended code.

The AutoFill part could be:

Range("B2").AutoFill Destination:=Range("B2:B" &
Range("A65536").End(xlUp).Row)

The whole thing turns into:

Range("A:K").Sort _
Key1:=Range("A2"), Order1:=xlAscending, _
Key2:=Range("G2"), Order2:=xlAscending, _
Key3:=Range("F2"), Order3:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
Columns("B:B").Insert Shift:=xlToRight
Range("B2").Formula = _
"=IF(COUNTIF(A:A,A2)1,""Duplicate"","""")"
Range("B2").AutoFill Destination:=Range("B2:B" &
Range("A65536").End(xlUp).Row)

Regards

Trevor


"o1darcie1o" wrote in message
...
I'm trying to set up a macro to do things I normally do by hand, over 15
or
so tabs in a workbook on a monthly basis.

On each sheet, I sort the sheet according to three columns. Then I insert
a
column at B. The formula I type by hand is
=IF(COUNTIF(A2:A30,A2)1,"Duplicate",""). Then I double click to fill
down,
copy, paste values, and sort descending by that new column. Except that
the
A30 changes each month!

I need to set up the macro so that I can reuse it each month on a new
workbook. Everything is the same, except the location of the last row of
information. I normally use
Selection.AutoFill Destination:=Range("B2:B" &
ActiveSheet.UsedRange.Rows.Count)
and that will auto fill to the last row of used data. But I don't know how
to incorporate that into the sort and the end of the range in the if
countif
formula.
It looks like this in my macro:
Range("A1:K30").Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range( _
"G2"), Order2:=xlAscending, Key3:=Range("F2"), Order3:=xlAscending,
_
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(RC[-1]:R[28]C[-1],RC[-1])1,""Duplicate"","""")"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B30")
Range("B2:B30").Select

Any help or tips for these two things would be most appreciated!!!!