Sub consolidate()
    
    Dim Destiny As Workbook
    Set Destiny = ThisWorkbook
    
    Dim Data As Workbook
    Dim Path As String
    
    Dim mrs As Variant
    mrs = Array( _
        "1 HUANUCO", _
        "2 AMARILIS", _
        "3 CHINCHAO", _
        "4 CHURUBAMBA", _
        "5 MARGOS", _
        "6 HUANCAPALLAC", _
        "7 SAN FRANCISCO DE CAYRAN", _
        "8 SAN PEDRO DE CHAULAN", _
        "9 SANTA MARIA DE VALLE", _
        "10 YARUMAYO", _
        "11 PILLCOMARCA", _
        "12 YACUS", _
        "13 SAN PABLO DE PILLAO" _
    )
    Dim mr As Variant
    
    Dim oFSO As Object
    Dim oFolder As Object
    Dim oFile As Object
    Dim mainDirectory As String
    
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    mainDirectory = GetFolderPath
    
    Dim currentRow As Integer
    Set firstCell = Application.InputBox(prompt:="Seleccione primera celda donde poner los datos:", Type:=8)
    currentRow = firstCell.Row
    
    For Each mr In mrs
        
        Debug.Print mainDirectory & "\" & mr
        
        Set oFolder = oFSO.GetFolder(mainDirectory & "\" & mr)
        
        For Each oFile In oFolder.Files
        
            Set Data = Workbooks.Open(oFile.Path)
            
            Debug.Print "- " & currentRow & " " & oFile.Name
            CopyValues Data, Destiny, currentRow
                        
            Data.Close savechanges:=False
            currentRow = currentRow + 1
            
        Next oFile
            
        currentRow = currentRow + 1
        
    Next mr
    
    MsgBox "SE COMPLETO LA CONSOLIDACION DE DATOS"
End Sub
Sub CopyValues(Data, Destiny, DestinyInitRow)
    Dim Selection As Range
    Dim Cel As Range
    Dim Counter As Integer
    
    Dim originCells As Variant
    originCells = Array( _
        "S12", "AC12", "AN12", "AZ12", "BM12", "BZ12", "S13", "AC13", "AN13", "AZ13", "BM13", "BZ13", "S14", "AC14", "AN14", "AZ14", "BM14", "BZ14", "S15", "AC15", "AN15", "AZ15", "BM15", "BZ15", "S16", "AC16", "AN16", "AZ16", "BM16", "BZ16", "S17", "AC17", "AN17", "AZ17", "BM17", "BZ17", "S18", "AC18", "AN18", "AZ18", "BM18", "BZ18", "S19", "AC19", "AN19", "AZ19", "BM19", "BZ19", _
        "S20", "AC20", "AN20", "AZ20", "BM20", "BZ20", "S21", "AC21", "AN21", "AZ21", "BM21", "BZ21", "S22", "AC22", "AN22", "AZ22", "BM22", "BZ22", "S23", "AC23", "AN23", "AZ23", "BM23", "BZ23", "S24", "AC24", "AN24", "AZ24", "BM24", "BZ24", "S25", "AC25", "AN25", "AZ25", "BM25", "BZ25", "S26", "AC26", "AN26", "AZ26", "BM26", "BZ26", "S27", "AC27", "AN27", "AZ27", "BM27", "BZ27", _
        "S32", "AC32", "AN32", "AZ32", "BM32", "BZ32", "S33", "AC33", "AN33", "AZ33", "BM33", "BZ33", "S39", "AC39", "AN39", "AZ39", "BM39", "BZ39", "S40", "AC40", "AN40", "AZ40", "BM40", "BZ40", "R44", "AB44", "AM44", "AY44", "BL44", "BY44", "R45", "AB45", "AM45", "AY45", "BL45", "BY45", "R46", "AB46", "AM46", "AY46", "BL46", "BY46", "Q52", "AA52", "AL52", "AX52", "BK52", "BX52", _
        "Q53", "AA53", "AL53", "AX53", "BK53", "BX53", "Q61", "AA61", "AL61", "AX61", "BK61", "BX61", "Q62", "AA62", "AL62", "AX62", "BK62", "BX62", "Q63", "AA63", "AL63", "AX63", "BK63", "BX63", "Q64", "AA64", "AL64", "AX64", "BK64", "BX64", "Q65", "AA65", "AL65", "AX65", "BK65", "BX65", "P72", "Z72", "AK72", "AW72", "BJ72", "BW72", "P73", "Z73", "AK73", "AW73", "BJ73", "BW73", _
        "P79", "Z79", "AK79", "AW79", "BJ79", "BW79", "P80", "Z80", "AK80", "AW80", "BJ80", "BW80", "P85", "Z85", "AK85", "AW85", "BJ85", "BW85", "P86", "Z86", "AK86", "AW86", "BJ86", "BW86", "BC91", "BO91", "CB91", "CQ91", "CZ91", "DC91", "BC92", "BO92", "CB92", "CQ92", "CZ92", "DC92", "BC93", "BO93", "CB93", "CQ93", "CZ93", "DC93", "BC94", "BO94", "CB94", "CQ94", "CZ94", "DC94", _
        "BC95", "BO95", "CB95", "CQ95", "CZ95", "DC95", "BC96", "BO96", "CB96", "CQ96", "CZ96", "DC96", "S102", "AC102", "AN102", "AZ102", "BM102", "BZ102", "S103", "AC103", "AN103", "AZ103", "BM103", "BZ103", "S104", "AC104", "AN104", "AZ104", "BM104", "BZ104", "S105", "AC105", "AN105", "AZ105", "BM105", "BZ105", "O110", "Y110", "O111", "Y111",  "O112", "Y112", "O113", "Y113", "O114", "Y114", "O115", "Y115", _
        "T122", "AE122", "AO122", "BB122", "BN122", "CA122", "T123", "AE123", "AO123", "BB123", "BN123", "CA123", "T124", "AE124", "AO124", "BB124", "BN124", "CA124", "T125", "AE125", "AO125", "BB125", "BN125", "CA125", "T126", "AE126", "AO126", "BB126", "BN126", "CA126", "T127", "AE127", "AO127", "BB127", "BN127", "CA127", "T128", "AE128", "AO128", "BB128", "BN128", "CA128", "T129", "AE129", "AO129", "BB129", "BN129", "CA129", _
        "T130", "AE130", "AO130", "BB130", "BN130", "CA130", "T131", "AE131", "AO131", "BB131", "BN131", "CA131", "U138", "AF138", "AQ138", "BD138", "BP138", "CC138", "U139", "AF139", "AQ139", "BD139", "BP139", "CC139", "U140", "AF140", "AQ140", "BD140", "BP140", "CC140", "U141", "AF141", "AQ141", "BD141", "BP141", "CC141", "BI147", "BU147", "CH147", "CY147", "DB147", "DE147", "BI148", "BU148", "CH148", "CY148", "DB148", "DE148", _
        "BI149", "BU149", "CH149", "CY149", "DB149", "DE149", "BI150", "BU150", "CH150", "CY150", "DB150", "DE150", "BI151", "BU151", "CH151", "CY151", "DB151", "DE151", "BI152", "BU152", "CH152", "CY152", "DB152", "DE152", "BI153", "BU153", "CH153", "CY153", "DB153", "DE153", "BI154", "BU154", "CH154", "CY154", "DB154", "DE154", "BH160", "BT160", "CG160", "CX160", "DA160", "DD160", "BH161", "BT161", "CG161", "CX161", "DA161", "DD161", _
        "BH162", "BT162", "CG162", "CX162", "DA162", "DD162", "BH163", "BT163", "CG163", "CX163", "DA163", "DD163", "BH164", "BT164", "CG164", "CX164", "DA164", "DD164", "BH165", "BT165", "CG165", "CX165", "DA165", "DD165", "BH166", "BT166", "CG166", "CX166", "DA166", "DD166", "BH167", "BT167", "CG167", "CX167", "DA167", "DD167", "X173", "AJ173", "AT173", "BG173", "BS173", "CF173", "X174", "AJ174", "AT174", "BG174", "BS174", "CF174", _
        "X175", "AJ175", "AT175", "BG175", "BS175", "CF175", "X176", "AJ176", "AT176", "BG176", "BS176", "CF176", "X181", "AJ181", "AT181", "BG181", "BS181", "CF181", "X183", "AJ183", "AT183", "BG183", "BS183", "CF183", "X185", "AJ185", "AT185", "BG185", "BS185", "CF185", "V196", "AH196", "AR196", "BE196", "BQ196", "CD196", "V197", "AH197", "AR197", "BE197", "BQ197", "CD197", "V203", "AH203", "AR203", "BE203", "BQ203", "CD203", _
        "W209", "AI209", "AS209", "BF209", "BR209", "CE209", "W210", "AI210", "AS210", "BF210", "BR210", "CE210", "V216", "AH216", "AR216", "BE216", "BQ216", "CD216", "V217", "AH217", "AR217", "BE217", "BQ217", "CD217", _
        "V221", "AH221", "AR221", "BE221", "BQ221", "CD221" _
    )
        
    Set Selection = Destiny.Sheets(1).Range("D" & DestinyInitRow & ":UM" & DestinyInitRow)
    
    For Each Cel In Selection.Cells
        Cel.Value = Data.Sheets(1).Range(originCells(Counter))
        
        If Len(Cel.Value) < 1 Then
            Cel.Value = 0
        End If
        
        Counter = Counter + 1
    Next Cel
End Sub
Function GetFolderPath() As String
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "SELECCIONA LA CARPETA DE DATOS PARA DANIOS NO TRANSMISIBLES"
        .AllowMultiSelect = False
        .InitialFileName = Application.DefaultFilePath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
    GetFolderPath = sItem
    Set fldr = Nothing
End Function