VBA macro to preprocess 2019 nCoV data

CleanWikiCovid.vb

Sub CleanWikiCovid()
    
    Selection.Hyperlinks.Delete
    Selection.UnMerge
    Range("A1").Select
    Cells.Find(What:="date", After:=ActiveCell, LookIn:=xlFormulas2, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    While Selection.Column > 1
        Selection.EntireColumn.Delete
        Cells.Find(What:="date", After:=ActiveCell, LookIn:=xlFormulas2, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Activate
    Wend
    
    While Selection.Row > 1
        Selection.EntireRow.Delete
        Selection.EntireRow.Delete
        Cells.Find(What:="date", After:=ActiveCell, LookIn:=xlFormulas2, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Activate
    Wend
    
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=TRIM(R[1]C&"" ""&R[2]C)"
    Range("B1").Select
    Selection.Copy
    Range("B2").Select
    Selection.End(xlToRight).Select
    Cells(1, Selection.Column).Select
    Range(Selection, Range("B1")).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Rows("2:3").Select
    Range("BY2").Activate
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    
    Cells.Replace What:="[*]", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False _
        , FormulaVersion:=xlReplaceFormula2
    Range("A1").Select
End Sub

Preprocess2019nCoVTimeSeries.vb

Sub Macro1()
'
' https://gisanddata.maps.arcgis.com/apps/opsdashboard/index.html#/bda7594740fd40299423467b48e9ecf6
' Unpivot time series table
'
    sheetName = ActiveSheet.Name
    
    Columns("F:F").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "ID"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("F3").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("F2:F3").Select
    ros = Range("A1").CurrentRegion.Rows.Count
    cols = Range("A1").CurrentRegion.Columns.Count
    r = "F2:F" & ros
    Selection.AutoFill Destination:=Range(r), Type:=xlFillDefault
    
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:= _
        Array(sheetName & "!R1C6:R" & ros & "C" & cols), Version:=6).CreatePivotTable _
        TableDestination:="", TableName:="PivotTable12", DefaultVersion:=6
        
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable12").DataPivotField.PivotItems( _
        "Sum of Value").Position = 1
    With ActiveSheet.PivotTables("PivotTable12").PivotFields("Column")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable12").RowAxisLayout xlTabularRow
    ActiveSheet.PivotTables("PivotTable12").RepeatAllLabels xlRepeatLabels
    Application.CutCopyMode = False
    With ActiveSheet.PivotTables("PivotTable12")
        .ColumnGrand = False
        .RowGrand = False
    End With
    ActiveSheet.PivotTables("PivotTable12").PivotFields("Row").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable12").PivotFields("Column").Subtotals = Array _
        (False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable12").PivotFields("Value").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    Range("A3").CurrentRegion.Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Name = "Sheet2"
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "m/d/yyyy"
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Report"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "ID"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = sheetName
    Range("A2").Select
    Sheets(sheetName).Select
    Range("B1").Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range("A1:E1").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("D1").Select
    ActiveSheet.Paste
    Sheets(sheetName).Select
    Columns("F:F").Select
    Application.CutCopyMode = False
    Selection.Cut
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight
    Sheets("Sheet2").Select
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "3"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "4"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "5"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "6"
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC2, " & sheetName & "!C1:C6, R1C, FALSE)"
    Range("D3").Select
    Selection.Copy
    ros = Range("A1").CurrentRegion.Rows.Count
    Range("D3:H" & ros).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("F:F").Select
    Range("F1885").Activate
    Application.CutCopyMode = False
    Selection.NumberFormat = "m/d/yyyy"
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Range("I1").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveCell.FormulaR1C1 = "Date"
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=TRUNC(RC[-8], 0)"
    Range("I2").Select
    Selection.Copy
    Range("H2").Select
    Selection.End(xlDown).Select
    Range("I" & ros).Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Range("F2").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add2 Key:=Range("F2"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet2").Sort
        .SetRange Range("A2:I" & ros)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Columns("D:D").Select
    Selection.Replace What:="0", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub