EmpresasAmigasAno

Sub ImportarComprasVendasParaTransComerEntreEmpAmigAno()
'
' ImportarComprasVendas Macro
'

'
    Dim x As Long

    Windows("TRANSAÇÕES_COMERC_ENTRE_EMP_AMIG_ANO.xlsm").Activate
    Sheets("Tabela Geral").Select
    Range("A1:AF1").Select
    Selection.AutoFilter
    Selection.AutoFilter
    Application.Goto Reference:="R2C1:R500000C32"
    Selection.ClearContents
    Application.Goto Reference:="R2C1"
    Range("A2").Select


    Workbooks.Open Filename:= _
        "G:\Depto. Financeiro\Tabelas\Mov CC Compras PHC + RTL + GSP.xlsx"

    ' Copiar Valores de compras >= 2014
    ActiveSheet.ListObjects("Tabela_Consulta_de_Mov_CC_Compras").Range.AutoFilter _
        Field:=2, Operator:=xlFilterValues, Criteria2:=Array(0, "12/31/2016", 0, _
        "12/31/2015", 0, "12/31/2014")
    Range("A2:X2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

    ' Colar Valores na folha TRANSAÇÕES_COMERC_ENTRE_EMP_AMIG_ANO
    ActiveCell.Select
    Windows("TRANSAÇÕES_COMERC_ENTRE_EMP_AMIG_ANO.xlsm").Activate
    Application.Goto Reference:="R2C1"
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    ' Eliminar Seleção e sair sem guardar
    Windows("Mov CC Compras PHC + RTL + GSP.xlsx").Activate
    Application.CutCopyMode = False
    ActiveWindow.Close savechanges:=False

    ' Copiar Valores de vendas >= 2014
    Workbooks.Open Filename:= _
        "G:\Depto. Financeiro\Tabelas\Mov CC Vendas PHC + RTL + GSP.xlsx"

    ActiveSheet.ListObjects("Tabela_Consulta_de_Mov_CC_Venda_todos").Range. _
        AutoFilter Field:=2, Operator:=xlFilterValues, Criteria2:=Array(0, _
        "12/31/2016", 0, "12/31/2015", 0, "12/31/2014")
    Range("A2:X2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

    ' Colar Valores na folha TRANSAÇÕES_COMERC_ENTRE_EMP_AMIG_ANO
    Windows("TRANSAÇÕES_COMERC_ENTRE_EMP_AMIG_ANO.xlsm").Activate
    Application.Goto Reference:="R1000000C2"
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, -1).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    ' Eliminar Seleção e sair sem guardar
    Windows("Mov CC Vendas PHC + RTL + GSP.xlsx").Activate
    Application.CutCopyMode = False
    ActiveWindow.Close savechanges:=False

    ' Copiar Valores de Compra/Venda < 2014
    Workbooks.Open Filename:= _
        "G:\Depto. Financeiro\Tabelas\TotalSomaCompraVenda.xlsx"

    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range("A2:X2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

    ' Colar Valores na folha TRANSAÇÕES_COMERC_ENTRE_EMP_AMIG_ANO
    Windows("TRANSAÇÕES_COMERC_ENTRE_EMP_AMIG_ANO.xlsm").Activate
    Selection.End(xlUp).Select
    x = ActiveSheet.UsedRange.Rows.Count
    Application.Goto Reference:="R" & x & "C2"
    ActiveCell.Offset(1, -1).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    ' Eliminar Seleção e sair sem guardar
    Windows("TotalSomaCompraVenda.xlsx").Activate
    Application.CutCopyMode = False
    ActiveWindow.Close savechanges:=False

    Windows("TRANSAÇÕES_COMERC_ENTRE_EMP_AMIG_ANO.xlsm").Activate







    Sheets("Config").Select
    Application.Goto Reference:="R1C25:R1C32"
    Selection.Copy
    Sheets("Tabela Geral").Select
    Application.Goto Reference:="R1000000C2"
    Selection.End(xlUp).Select
    ActiveCell.Offset(0, 23).Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False


    Calculate


    Application.Goto Reference:="R1C25"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False











    Application.Goto Reference:="R1C1"
    Selection.End(xlDown).Select
    LinhaF = Selection.Row




    Application.Goto Reference:="R1C1"
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Sheets("Tabela Geral").Select

    ActiveWorkbook.Worksheets("Tabela Geral").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Tabela Geral").Sort.SortFields.Add Key:=Range( _
        "B2:B" & LinhaF), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Tabela Geral").Sort.SortFields.Add Key:=Range( _
        "AD2:AD" & LinhaF), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Tabela Geral").Sort.SortFields.Add Key:=Range( _
        "H2:H" & LinhaF), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Tabela Geral").Sort.SortFields.Add Key:=Range( _
        "I2:I" & LinhaF), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Tabela Geral").Sort
        .SetRange Range("A1:AF" & LinhaF)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    Calculate


    Sheets("Analise").Select
    ActiveSheet.PivotTables("Tabela dinâmica1").PivotCache.Refresh

    Call FormatarAnalises
End Sub