Microsoft Office Forums - View Single Post - Consolidate

macropod · 07-03-2012, 04:42 AM

hi Jirik,

Are you sure the source files are where they should be? Do they have the correct names and extensions? Have the source files been converted to the xls(x/m) formats?

FWIW, I believe your code could be made simpler and more efficient:

Code:

Sub Konsolidace()
Dim Cesta As String
Cesta = ThisWorkbook.Path
With Sheets("Výsledovka")
  .Unprotect
  .Range("E13").Consolidate Sources:=Array( _
    Cesta & "\[F12_K00_Regiony.xlsm]Výsledovka!R13C5:R119C18", _
    Cesta & "\[F12-105151.xlsx]Výsledovka!R13C5:R119C18", _
    Cesta & "\[F12-120100.xlsx]Výsledovka!R13C5:R119C18", _
    Cesta & "\[F12_K_HQ.xlsm]Výsledovka!R13C5:R119C18"), _
    Function:=xlSum, TopRow:=False, LeftColumn:=False, CreateLinks:=False
  .Range("E164").Consolidate Sources:=Array( _
    Cesta & "\[F12_K00_Regiony.xlsm]Výsledovka!R164C5:R164C18", _
    Cesta & "\[F12-105151.xlsx]Výsledovka!R164C5:R164C18", _
    Cesta & "\[F12-120100.xlsx]Výsledovka!R164C5:R164C18", _
    Cesta & "\[F12_K_HQ.xlsm]Výsledovka!R164C5:R164C18"), _
    Function:=xlSum, TopRow:=False, LeftColumn:=False, CreateLinks:=False
  .Protect
End With
End Sub