Sub WorkbooksConsolidate()
Rem Set the summation area to be the sheet name / cell area; the sheet name / cell area.Const Setting As String = "Sheet1/A1:G6; Sheet1/A8:E8; Sheet1/"F8:G8; Sheet2/A1:G3; Sheet2/A5:G5 "Const FOLDER_NAME As String = folderDim StartTime As VarianTDim UsedTime As VariantStartTime = VBA.TimerAppSettings TrueDim Wb As WorkBookDim Sht As WorksheetDim Dic As ObjectDim Key As StringDim OneKeyDim BrrDim Arr As VariantDim Rng As RangeDim FilePaths, FilePathDim FolderPath As StrinGDim OpenWb As WorkbookDim OpenSht As WorksheetSet Dic = CreateObject ("Scri")Pting.Dictionary ""Set Wb = Application.ThisWorkbookFolderPath = Wb.Path & "" & FOLDER_NAME & "".Dim SheetName, RngAddressDim Areas, OneAreaAreas = Split (Sett)Ing, ";")For Each OneArea In AreasSheetName = Split (OneArea, "/") (0)RngAddresS = Split (OneArea, "/") (1)Resolving address initialization arrayOn Error Resume NextSet Sht = Wb.WorKsheets (SheetName)If Err.Number = 9 ThenMsgBox "there is no" & "in the current workbook; SheetName &Amp, "workbook!", vbInformation, "Information".GoTo ErrorExitEnd IfOn ErrOr GoTo 0Set Rng = Sht.Range (RngAddress)Rng.ClearContentsArr = RNg.ValueDebug.Print SheetName; ""; RngAddressDoIf Dic.Exists (Sheet)Name) = False Then Exit DoSheetName = SheetName & @ @LoopDic (SheeTName) = Array (RngAddress, Arr)Next OneAreaFilePaths = FsoGetFiles (Fold)ErPath, "*.xls*")If FilePaths (1) = "None" ThenMsgBox "specified folder does not find any workbook!", vbInformation."Information"GoTo ErrorExitEnd IfFor Each FilePath In FilePathsSetOpenWb = Application.Workbooks.Open (FilePath)For Each OneKey In Dic.KeysSheetNAme = Replace (OneKey, "@", "")On Error Resume NextSet OpenSht = OpenWb.WorKsheets (SheetName)If Err.Number = 9 ThenMsgBox "opens the workbook without the name" & "; SheEtName & "workbook!", vbInformation, "Information".OpenWb.Close FalseGoTo ErrorExitEnd IfOn Error GoTo 0Ar = Dic (OneKey)RngAddress = Ar (0)Arr = Ar (1)Set Rng = OpenSht.Range (RngAddress)Brr = Rng.ValueIf Rng.Cells.Count > 1 ThenFor I = LBound (Arr) To UBound (Arr)For J = LBound (Arr, 2) To UBound (Arr, 2)If IsNumeric (Brr (I)J)) Then'only when numbers are added.Arr (I, J) = Arr (I, J)+ Brr (I, J)ElseMsgBox "Workbook:" & FilePath& vbCr &"Worksheet:" & SheetName & vbCr &"Cell:" & Rng.Cells (I, J).Address & "data is not numeric, not cumulative."GoTo ErrorExitEnd IfNext JNext IElseIf IsNumeric (Brr) Then'only when numbers are added.Arr = Arr + BrrElseMsgBox "work"Book: "& FilePath & vbCr &""Worksheet:" & SheetName & vbCr &"Cell:" & Rng.Cells (I, J).Address & "data is not numeric, not cumulative."GoTo ErrorExitEnd IfEnd IfUpdate sum dataAr (1) = ArrDic (OneKey) = ArNext OneKeyOpenWb.CloSe FalseNext FilePathFor Each OneKey In Dic.KeysSheetName = Replace (OneKey)."@", "")Ar = Dic (OneKey)RngAddress = Ar (0)Arr = Ar (1)Set Sht = WB.Worksheets (SheetName)Set Rng = Sht.Range (RngAddress)Rng.Value = ArrNext OneKeyUsedTime = VBA.Timer - StartTimeDebug.Print "UsedTime:" & Format (UsedTim)E, "#0.0000 Seconds")'MsgBox "UsedTime:" & Format (UsedTime, "#0.0000 Seconds")ERrorExit:Set Dic = NothingSet Wb = NothingSet Sht = NothingSet Rng = NothingSet OpenWb = NothingSet OpenSht = NothingErase ArrErase BrrErase ArAppSettIngs FalseEnd SubFunction FsoGetFiles (ByVal FolderPath As String, ByVal Pattern As String, OptionaL ComplementPattern As String = "" As String ()Dim Arr () As StringDim FSO As ObjectDiM ThisFolder As ObjectDim OneFile As ObjectReDim Arr (1 To 1)Arr (1) = "None"Dim INdex As LongIndex = 0Set FSO = CreateObject ("Scripting.FileSystemObject")On Error GoTO ErrorExitSet ThisFolder = FSO.getfolder (FolderPath)If Err.Number < > 0 Then Exit FuNctionFor Each OneFile In ThisFolder.FilesIf OneFile.Name Like Pattern ThenIf Len (ComplementPattern) > 0 ThenIf Not OneFile.Name Like ComplementPattern THenIndex = Index + 1ReDim Preserve Arr (1 To Index)Arr (Index) = OneFile.PathEnd IfElseIndex= Index + 1ReDim Preserve Arr (1 To Index)Arr (Index) = OneFile.PatHEnd IfEnd IfNext OneFileErrorExit:FsoGetFiles = ArrErase ArrSet FSO = NothingSet ThisFolder = NothingSet OneFile = NothingEnd FunctionSub AppSetTings (Optional IsStart As Boolean = True)Application.ScreenUpdating = IIf (IsStart, False, True)Application.DisplayAlerts = IIf (IsStart, False, True)Application.Calculation = IIf (IsStart)XlCalculationManual, xlCalculationAutomatic)Application.StatusBar = IIf (IsStart, "> > >"); > > > > > Macro Is Running> > > > >;;;;; ",", "End Sub
