Sub Ìàêðîñ1()
'
' Ìàêðîñ1 Ìàêðîñ
'

'
    ActiveWorkbook.Queries("Otchet").Delete
    ActiveWorkbook.Queries.Add Name:="Otchet", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Èñòî÷íèê = Csv.Document(File.Contents(""E:\Otchet.csv""),[Delimiter="","",Encoding=65001])," & Chr(13) & "" & Chr(10) & "    #""Èçìåíåííûé òèï"" = Table.TransformColumnTypes(Èñòî÷íèê,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Óäàëåííûå ñòîëáöû"" = Table.RemoveColumns(#""Èçìåíå" & _
        "ííûé òèï"",{""Column1""})," & Chr(13) & "" & Chr(10) & "    #""Ïîâûøåííûå çàãîëîâêè"" = Table.PromoteHeaders(#""Óäàëåííûå ñòîëáöû"")" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Ïîâûøåííûå çàãîëîâêè"""
    Sheets.Add After:=ActiveSheet
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Otchet" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Otchet]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .ListObject.DisplayName = ("Otchet")
        .Refresh BackgroundQuery:=False
    End With
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    ActiveSheet.ListObjects("Otchet").TableStyle = "TableStyleLight14"
    Application.CommandBars("Workbook Queries").Visible = False
    Range("E6").Select
    Application.CommandBars("Workbook Queries").Visible = False
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-2],C[-2])"
    Columns("D:D").Select
    Selection.Copy
    Columns("E:E").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("D:D").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("D:D").Select
    Selection.Cut
    Range("Otchet[[#Headers],[Material]]").Select
    ActiveWindow.SmallScroll Down:=39
    Range("C56").Select
    ActiveSheet.ListObjects("Otchet").Resize Range("$A$1:$D$55")
    Range("C56").Select
    ActiveWindow.SmallScroll Down:=-69
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Range("Otchet[#All]").Select
    ActiveSheet.Range("Otchet[#Âñå]").RemoveDuplicates Columns:=Array(1, 2, 3, 4), _
        Header:=xlYes
    Range("Îò÷åò").Select
    ActiveWorkbook.Worksheets("Ëèñò2").ListObjects("Otchet").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Ëèñò2").ListObjects("Otchet").Sort.SortFields.Add _
        Key:=Range("Îò÷åò[Layer]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Ëèñò2").ListObjects("Îò÷åò").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("Otchet[[#Headers],[Ñòîëáåö1]]").Select
    ActiveCell.FormulaR1C1 = "Êîë"
    Range("B2").Select
    ActiveWindow.SmallScroll Down:=-9
    ActiveWorkbook.Queries("Otchet").Delete
End Sub

ActiveSheet.Range("Îò÷åò[#Âñå]").RemoveDuplicates Columns:=Array(1, 2, 3, 4), _
        Header:=xlYes