CODEWITHSUNDEEP
excelvba
Silent Observer
Silent Observer

Reputation: 69

Consolidate specific sheets from different workbooks into one in sheet

I have a single workbook which is edited by 6-7 different people. There are counts given be each person in the sheet and I am looking for a way where I can merge all the sheets and find the sum of the total count. For eg,

This is the sheet in 1st workbook,

A        B          c
10       15         10

The sheet in the 2nd workbook,

A        B          c
7       10          9

And it is similar for all the workbooks. I want the final consolidated one to have the sum of all the values in sheets of each workbook,

A        B          c
17       25         19

I thought one way to do this is to, put all the excel workbooks into a same folder and use the following code to merge it and then use a macro to count it.

Sub GetSheets()
Path = "C:\Users\username\Downloads\New folder"
Filename = Dir(Path & "*.xlsx")
  Do While Filename <> ""
  Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
     For Each Sheet In ActiveWorkbook.Sheets
     Sheet.Copy After:=ThisWorkbook.Sheets(1)
  Next Sheet
     Workbooks(Filename).Close
     Filename = Dir()
  Loop
End Sub

But for some reason this code is getting executed without any errors but has no effect in the consolidated workbook. It is not merging the sheets of the remaining workbooks.

Can anybody tell me what is the error I am making here?

Also is there any alternate way to find the consolidated sum?

thanks

Upvotes: 1

Views: 1200

Answers (2)

ABEYMJ
ABEYMJ

Reputation: 1

If you are executing the vba code in an excel sheet which is in the same path(folder) as that of the sheets you want to combine ,this would happen. Try executing the vba in a new excel workbook.

Upvotes: 0

Dirk Reichel
Dirk Reichel

Reputation: 7979

Asuming you still want to go with DIR (you need to input checks for name if there are also other files inside the folder)
Tested it at my pc and it worked perfectly:

Sub SumWB()
  Dim Arr(2) As Long, MyWB As Workbook, fStr as String
  Const Folder = "C:\NewFolder\"
  fStr = Dir(Folder)
  While (file <> "")
    Set MyWB = Workbooks.Open(Folder & fStr, , True)
    Arr(0) = Arr(0) + MyWB.Sheets(1).Range("A1").Value
    Arr(1) = Arr(1) + MyWB.Sheets(1).Range("B1").Value
    Arr(2) = Arr(2) + MyWB.Sheets(1).Range("C1").Value
    MyWB.Close
    file = Dir
  Wend
  Debug.Print Arr(0) & " - " & Arr(1) & " - " & Arr(2)
End Sub

Upvotes: 1

Related Questions