Excel Macro to use cells as a autofilter

[John Romano]

Whats up guys!

I've been trying to record this macro, but its showing as a real challenge. I have 2 spreadsheets and I need to sum the values from one to other, so here is my idea : in one sheet i will use 3 cells in a line as an autofilter to the other spreadsheet, so 2 cells in the first sheet would be copied and pasted and a filtered as an equal value into the other spreadsheet(its numer and a name) and the last one would be filtered as equal or greater than that value.With all filters up in the second spreadshet, i would select an entire column, select, copy ,transpose and paste and finally i would sum all these values in the first spreadsheet.

Well here is the code as a tried to record

ActiveCell.Offset(0, -3).Range("A1").Select
Selection.Copy
Windows("170828 AMINO-X Payback (Sales per Client 2014-2016) - MLara.xlsx"). _
Activate
ActiveSheet.Range("$A$1:$AQ$10346").AutoFilter Field:=12, Criteria1:= _
"=ALIBEM ALIMENTOS S.A.", Operator:=xlOr, Criteria2:= _
"=ALIBEM COMERCIAL DE ALIMENTOS LTDA"
Windows("Exercício - Lar.xlsx").Activate
ActiveCell.Offset(0, 3).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("170828 AMINO-X Payback (Sales per Client 2014-2016) - MLara.xlsx"). _
Activate
ActiveSheet.Range("$A$1:$AQ$10346").AutoFilter Field:=3, Criteria1:="2"
Windows("Exercício - Lar.xlsx").Activate
ActiveCell.Offset(0, -1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("170828 AMINO-X Payback (Sales per Client 2014-2016) - MLara.xlsx"). _
Activate
ActiveSheet.Range("$A$1:$AQ$10346").AutoFilter Field:=1, Criteria1:= _
">=201512", Operator:=xlAnd
ActiveCell.Offset(7543, 18).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(20, 0).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(2713, 0).Range("A1").Select
Selection.End(xlDown).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Windows("Exercício - Lar.xlsx").Activate
ActiveCell.Offset(0, 18).Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=SUM('[170828 AMINO-X Payback (Sales per Client 2014-2016) - MLara.xlsx]Data'!R1048576C1:R1048576C40)"
ActiveCell.Select

As you guys can see above, the recording tool set it as a value, i tried to see if i can code something to use the active cell values as criteria and i've come with that:
Criteria1:="*" & ActiveCell & "*"

But its not working! Do you guys have any ideia how to solve that problem?

Thanks a lot