Sub ImportarDados()
'
    Application.DisplayAlerts = False ' desativa as perguntas do excel ( salvar arquivo ? )
    Application.ScreenUpdating = False ' desabilita a visualizaçao da macro


    'importa cotaçoes
    Sheets("Cotações").Select
    Cells.ClearContents
    Workbooks.Open ("https://docs.google.com/spreadsheets/d/e/2PACX-1vQBUuo9if0TUUTofNLVxq-aFnO4OJC4ht8DHxOD99jC79CwAhBXwccOUAFVSfZvmB-bZ-lIFocTMj9c/pub?output=xlsx")
    Cells.Copy
    Windows("Invest2.xlsm").Activate
    Cells.Select
    ActiveSheet.Paste
    Windows("pub").Close

    'importa tesouro direto
    Sheets("TTD").Select
    Cells.ClearContents
    Workbooks.Open ("https://docs.google.com/spreadsheets/d/e/2PACX-1vRmCnQdV5KZ4rXaJ9Md8CkIWWX5bP0tpH-EeQCBa7UHqfbkaBj9NTxpf1GCMgl0oSOL_izk4PAVfjOD/pub?output=xlsx")
    Sheets("TTD").Select
    Cells.Copy
    Windows("Invest2.xlsm").Activate
    Cells.Select
    ActiveSheet.Paste
    Windows("pub").Close

    Sheets("Carteira").Select
End Sub


Sub AtualizarNetWorth()

    Range("C2").Select
    Selection.End(xlDown).Offset(1, 0).Select
    ActiveCell.Value = Range("Track!B8").Value

    Range("D2").Select
    Selection.End(xlDown).Offset(1, 0).Select
    ActiveCell.Value = Range("Track!B7").Value

    Range("E2").Select
    Selection.End(xlDown).Offset(1, 0).Select
    ActiveCell.Value = Range("Track!B5").Value

    Range("F2").Select
    Selection.End(xlDown).Offset(1, 0).Select
    ActiveCell.Value = Range("Track!B6").Value



End Sub
Sub D2Macro()

    Dim data As Variant
    Dim açoes As Variant

    data = Range("B3").Value
    açoes = Range("B4").Value

    Range("F3").Select
    Selection.End(xlDown).Select

    If ActiveCell.Value <> data Then
        Range("B20").Value = açoes

        Range("F2").Select
        Selection.End(xlDown).Offset(1, 0).Select
        ActiveCell.Value = data
        ActiveCell.Offset(0, 1).Value = açoes

        Application.CutCopyMode = False
        Range("A1").Select
    Else
    End If
    Range("A3").Select

End Sub

Sub DMacro()

    Dim Valores(10) As Variant
    Dim data As Variant

    data = Range("C3").Value

    Range("E3").Select
    Selection.End(xlDown).Select

    If ActiveCell.Value <> data Then
        For i = 3 To 10
            Valores(i) = Cells(i, 3).Value
            Cells(2, i + 2).Select
            Selection.End(xlDown).Offset(1, 0).Select
            ActiveCell.Value = Valores(i)
        Next i
    Else
    End If

    Range("A3").Select
End Sub

Sub AtualizarInfo()

    Range("B2").Select
    Selection.End(xlDown).Offset(1, 0).Select
    ActiveCell.Value = Range("Track!B1")
    ActiveCell.Offset(0, 2).Value = Range("Track!B2")
    ActiveCell.Offset(0, 4).Value = Range("Track!B3")
    ActiveCell.Offset(0, 6).Value = Range("Track!B4")
    ActiveCell.Offset(0, 8).Value = Range("Track!B5")

End Sub

Sub EnviarRelatorio()


    Dim outlook As Object
    Dim newEmail As Object
    Dim xInspect As Object
    Dim pageEditor As Object
    Dim VariaçaoDia As Variant

    'data
    dia = Day(Range("F2"))
    mes = Month(Range("F2"))
    ano = Year(Range("F2"))
    data = Range("F2")

    If dia < 10 Then
        dia = "0" & dia
    End If

    If mes < 10 Then
        mes = "0" & mes
    End If

    VariaçaoDia = Range("G2").Value * 100

    'ExportarPDF
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "D:\Desktop\investimentos\Relatórios\Relatório" & ano & mes & dia & ".pdf", Quality:=xlQualityStandard _
        , IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True


    Set outlook = CreateObject("Outlook.Application")
    Set newEmail = outlook.CreateItem(0)

    With newEmail
        .To = "renan_morettopereira@hotmail.com"
        .CC = "renan@tropicoinvest.com"
        .BCC = ""
        .Subject = "Relatório " & Format(Range("F2"), "dd-mm-yy") & " " & VariaçaoDia & "%"
        .Attachments.Add "D:\Desktop\investimentos\Relatórios\Relatório" & ano & mes & dia & ".pdf"
        .Attachments.Add "D:\Desktop\investimentos\Invest2.xlsm"
        .HTMLBody = ""
        .display

        Set xInspect = newEmail.GetInspector
        Set pageEditor = xInspect.WordEditor

        Sheets("Resumo").Range("F2:P60").Copy

        pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
        pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText)
        .display
        .Send
        Set pageEditor = Nothing
        Set xInspect = Nothing
    End With

    Set newEmail = Nothing
    Set outlook = Nothing
    Application.CutCopyMode = False








End Sub
Sub AtualizarVarTabela()

    'data
    Sheets("Resumo").Select
    dia = Day(Range("F2"))
    mes = Month(Range("F2"))
    ano = Year(Range("F2"))
    data = Range("F2")
    Dim dmes As Date
    Dim dano As Date
    Dim d6m As Date
    Dim d12m As Date
    Dim vames As Variant
    Dim vaano As Variant
    Dim va6m As Variant
    Dim va12m As Variant
    Dim vfiimes As Variant
    Dim vfiiano As Variant
    Dim vfii6m As Variant
    Dim vfii12m As Variant
    Dim vinvmes As Variant
    Dim vinvano As Variant
    Dim vinv6m As Variant
    Dim vinv12m As Variant
    Dim lin As Integer
    Dim var As Variant


    If dia < 10 Then
        dia = "0" & dia
    End If

    If mes < 10 Then
        mes = "0" & mes
    End If

    d6m = DateAdd("m", -6, data)
    d12m = DateAdd("yyyy", -1, data)
    dmes = DateSerial(ano, mes, 1)
    dano = DateSerial(ano, 1, 1)

    Sheets("D").Select
    Range("E3").Select
    Selection.End(xlDown).Select
    lin = ActiveCell.Row

    'açoes
    vmes = loopv(lin, 15, dmes)
    vano = loopv(lin, 15, dano)
    v6m = loopv(lin, 15, d6m)
    v12m = loopv(lin, 15, d12m)

    'fiis
    vfiimes = loopv(lin, 17, dmes)
    vfiiano = loopv(lin, 17, dano)
    vfii6m = loopv(lin, 17, d6m)
    vfii12m = loopv(lin, 17, d12m)

    'invest
    vinvmes = loopv(lin, 25, dmes)
    vinvano = loopv(lin, 25, dano)
    vinv6m = loopv(lin, 25, d6m)
    vinv12m = loopv(lin, 25, d12m)

    'colar na tabela
    Sheets("Resumo").Select
    Range("L1000").Select
    Selection.End(xlUp).Select

    ActiveCell.Value = vinvmes
    ActiveCell.Offset(0, 1).Value = vinvano
    ActiveCell.Offset(0, 2).Value = vinv6m
    ActiveCell.Offset(0, 3).Value = vinv12m

    ActiveCell.Offset(-1, 0).Value = vfiimes
    ActiveCell.Offset(-1, 1).Value = vfiiano
    ActiveCell.Offset(-1, 2).Value = vfii6m
    ActiveCell.Offset(-1, 3).Value = vfii12m

    ActiveCell.Offset(-2, 0).Value = vmes
    ActiveCell.Offset(-2, 1).Value = vano
    ActiveCell.Offset(-2, 2).Value = v6m
    ActiveCell.Offset(-2, 3).Value = v12m

End Sub
Public Function loopv(lin As Integer, col As Integer, ddata As Date) As Variant

    var = 0
    For i = 1 To lin
        If Cells(i, 5) > ddata Then
            var = ((1 + var) * (1 + Cells(i, col))) - 1
        Else
        End If
    Next i
    loopv = var

End Function


Sub AtualizarPlanilhaMain()

    Sheets("Carteira").Select
    Call ImportarDados

    Sheets("D").Select
    Call DMacro

    Sheets("D2").Select
    Call D2Macro

    Sheets("Resumo").Select
    Call AtualizarVarTabela

    MsgBox "Planilha atualizada!"

End Sub