Padronização de tabela no Excel (WIP)

StandardNfeReport.bas

Sub MainCreateStandardReport()
    Application.ScreenUpdating = False

    Dim originalDataSheetName As String
    Dim originalFileName As String
    Dim originalFilePath As String
    Dim filePath As String
    Dim pathSep As String
    Dim fileToSave As String

    If InStr(Application.OperatingSystem, "Windows") Then
        pathSep = "\"
    Else
        pathSep = "/"
    End If

    originalFileName = ActiveWorkbook.Name
    originalFilePath = ActiveWorkbook.FullName
    filePath = ActiveWorkbook.Path
    originalDataSheetName = ActiveSheet.Name

    If InStr(originalFilePath, ".zip") Then
        MsgBox "Parece que o arquivo não foi extraido do .zip."
        End
    End If

    If LCase(Right(originalFileName, 4)) = ".xls" And ActiveCell.Value = "OPERACAO" Then

        ActiveSheet.range("A1").Select

        Call SelectAllAndMakeTable
        Call HideUnwantedColumnsFromData
        Call RenameAndCreateSheets
        Call CreatePivotTable

        fileToSave = filePath & pathSep & "NFE.xlsx"

        If Dir(fileToSave) <> "" Then
            Kill fileToSave
        End If

        ActiveWindow.DisplayGridlines = False
        ActiveWorkbook.SaveAs FileName:=fileToSave, FileFormat:=xlOpenXMLWorkbook
        
        Kill originalFilePath
    Else
        MsgBox "Arquivo não permitido. Verifique se a macro está sendo executada no arquivo correto."
    End If

    Application.ScreenUpdating = True
End Sub

Sub SelectAllAndMakeTable()
    Dim dataTable As ListObject
    Dim dataRange As range

    Set dataRange = range(range("A1"), range("A1").SpecialCells(xlLastCell))
    Set dataTable = ActiveSheet.ListObjects.Add(xlSrcRange, dataRange, , xlYes)

    Range("Q2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Style = "Currency"

    Range("R2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Style = "Currency"

    Range("S2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Style = "Currency"

    Range("T2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Style = "Currency"

    Range("Y2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Style = "Currency"

    Range("Z2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Style = "Currency"

    Range("A1").Select
End Sub

Sub HideUnwantedColumnsFromData()
    Dim columnsToHide As String
    columnsToHide = "CDEFHIKLUVWX"
    For counter = 1 To Len(columnsToHide)
        Columns(Mid(columnsToHide, counter, 1)).Hidden = True
    Next
    Columns("AA").Hidden = True
    Columns("AB").Hidden = True
    Columns("AC").Hidden = True
    Columns("AD").Hidden = True
    Columns("AE").Hidden = True
End Sub

Sub RenameAndCreateSheets()
    On Error Resume Next
    Application.DisplayAlerts = False
    ActiveSheet.Name = "DADOS"
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Name = "TABELA"
    Application.DisplayAlerts = True
End Sub

Sub CreatePivotTable()
    Dim pivotTableSheet As Worksheet
    Dim dataSheet As Worksheet
    Dim pivotTableCache As PivotCache
    Dim pivotTableName As PivotTable
    Dim sourceDataRange As range
    Dim dataRangeLastRow As Long
    Dim dataRangeLastCol As Long
    Dim numberFormat As String

    numberFormat = "#,##0.00;[Red]#,##0.00"

    Set pivotTableSheet = Worksheets("TABELA")
    Set dataSheet = Worksheets("DADOS")

    dataRangeLastRow = dataSheet.Cells(Rows.Count, 1).End(xlUp).Row
    dataRangeLastCol = dataSheet.Cells(1, Columns.Count).End(xlToLeft).Column

    Set sourceDataRange = dataSheet.Cells(1, 1).Resize(dataRangeLastRow, dataRangeLastCol)

    Set pivotTableCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, _
        SourceData:=Sheets("DADOS").range("A1").CurrentRegion)
    Set pivotTableName = pivotTableCache.CreatePivotTable( _
        TableDestination:=ActiveWorkbook.Worksheets("TABELA").range("A1"), TableName:="VALORES")

    With ActiveSheet.PivotTables("VALORES").PivotFields("OPERACAO")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("VALORES").PivotFields("UFDESTINATARIO")
        .Orientation = xlRowField
        .Position = 2
    End With

    With ActiveSheet.PivotTables("VALORES").PivotFields("BCICMS")
        .Orientation = xlDataField
        .Position = 1
        .Function = xlSum
        .NumberFormat = numberFormat
        .Name = "BC ICMS"
    End With
    With ActiveSheet.PivotTables("VALORES").PivotFields("VALORTOTALICMS")
        .Orientation = xlDataField
        .Position = 2
        .Function = xlSum
        .NumberFormat = numberFormat
        .Name = "TOTAL ICMS"
    End With
    With ActiveSheet.PivotTables("VALORES").PivotFields("BCICMS_ST")
        .Orientation = xlDataField
        .Position = 3
        .Function = xlSum
        .NumberFormat = numberFormat
        .Name = "BC ICMS ST"
    End With
    With ActiveSheet.PivotTables("VALORES").PivotFields("TOTALICMSST")
        .Orientation = xlDataField
        .Position = 4
        .Function = xlSum
        .NumberFormat = numberFormat
        .Name = "TOTAL ICMS ST"
    End With
    With ActiveSheet.PivotTables("VALORES").PivotFields("IPI")
        .Orientation = xlDataField
        .Position = 5
        .Function = xlSum
        .NumberFormat = numberFormat
        .Name = "TOTAL IPI"
    End With
    With ActiveSheet.PivotTables("VALORES").PivotFields("VALORTOTALNOTA")
        .Orientation = xlDataField
        .Position = 6
        .Function = xlSum
        .NumberFormat = numberFormat
        .Name = "TOTAL NOTAS"
    End With

    ActiveSheet.PivotTables("VALORES").PivotFields("SITUACAO").Orientation = xlPageField
    ActiveSheet.PivotTables("VALORES").PivotFields("SITUACAO").Position = 1
    ActiveSheet.PivotTables("VALORES").PivotFields("SITUACAO").ClearAllFilters
    ActiveSheet.PivotTables("VALORES").PivotFields("SITUACAO").CurrentPage = "Autorizada"
End Sub