1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
| Sub never2015()
'filter dealer who have never bought in the year
Sheets("current_month").Range("currentmonth[[#Headers],[#Data]]").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Sheets("neveryear").Range("A1:A2"), _
CopyToRange:=Sheets("neveryear").Range("A5:B5"), _
Unique:=False
'added retail et EDS columns
'found figures
Sheets("neveryear").Range("c6").FormulaR1C1 = _
"=ROUND(INDEX(Totalprojection,MATCH(RC2,PROJECTION_DEALERS_CODE,0),7),0)"
Sheets("neveryear").Range("d6").FormulaR1C1 = _
"=ROUND(INDEX(Totalprojection,MATCH(RC2,PROJECTION_DEALERS_CODE,0),29),0)"
dernligne = Sheets("neveryear").[A5].End(xlDown).Row 'A5 mieux si tu n'as qu'une ligne apres l'en-tete
'pasted figures
Sheets("neveryear").Range("C6:D6").AutoFill _
Destination:=Range("C6:D" & dernligne), _
Type:=xlFillDefault
End Sub |
Partager