PLEASE HELP. I am new to VBA and was trying this code to get around a problem. I have nearly 20 workbooks that contain 23 sheets each. I have prepared a code in a separate "Summary" workbook that should work in a way that it goes and searches for a specific range in those 20 workbooks and the subsheets and sums values cell by cell in this range. I have come up with a code that works for 1 cell but cannot get it working on a range nor in multiple sheets. For illustration please see how the files look like. The summary sheet looks just like the sample but does not have the 1s in - it's empty for the summarization. Any help with the code to get it working on a range and on multiple sheets will be welcome! If any clarification is needed I will be of help! Thanks in advance!
PS: the macro is available in the sample sheet as well Sample.xlsm
'Summarization macro - is searching for a specific cell in every workbook in the folder and sums it up in a predefined cell
Dim wb As Workbook, TheFile As String
Dim MyPath As String, TheSum As Double
Dim MyArray As Variant
MyArray = Array("CLOOS-1323.003", "CLOOS-1324.004", "CLOOS-1325.005")
TheSum = 0
MyPath = ThisWorkbook.Path
TheFile = Dir("*.xlsx")
Do While TheFile <> ""
If TheFile <> ThisWorkbook.Name Then
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
TheSum = TheSum + Sheets("1630.001 Montáž koles.jednot.").Range("G12:028")
TheFile = Dir
Sheets(MyArray).Range("G12:028") = TheSum