Macro develop

[Bahaa]

I am developing a macro for multiple excel sheet where in each sheet i have rows and columns
Bank Account Week1 Week2 Week3 Week4 Week5 Average
Account#1 10 10 10 10 10 =(10+10+10+10+10/5

Account#2 50 60 70 80 90

What i need in that macro is to consolidate for me 460 sheets related to different entities into one where it should consolidate all the input in each sheet in one sheet showing an aggregate break down view :

Entity 1
Account#1 10 10 10 10 10 =(10+10+10+10+10/5
Account#2 50 60 70 80 90

Entity 2
Account#1 10 10 10 10 10 =(10+10+10+10+10/5
Account#2 50 60 70 80 90

Entity 3
Account#1 10 10 10 10 10 =(10+10+10+10+10/5
Account#2 50 60 70 80 90


Thansk

 

[Bahaa]

Just wanted to simplify the request
I have multiple sheets (460 sheets) and need to consolidate it in one sheet after adding certain inputs using macro / VBA code

Sheet 1 Data

Entity 1​	Week1​	Week2​	Week3​	Week4​	Average​
Account#1​	1​	5​	1​	6​	3.25​
Account#2​	1​	1​	1​	1​	1​
					4.25​

Sheet 2 Data

Entity 2​	Week1​	Week2​	Week3​	Week4​	Average​
Account#1​	1​	5​	1​	6​	3.25​
Account#2​	1​	1​	1​	1​	1​
					4.25​

Consolidate sheet using macro button
To read from the 460 sheets (each entity data) and show it in this way

Entity 1​	Week1​	Week2​	Week3​	Week4​	Average​
Account#1​	1​	5​	1​	6​	3.25​
Account#2​	1​	1​	1​	1​	1​
					4.25​
Entity 2​	Week1​	Week2​	Week3​	Week4​	Average​
Account#1​	1​	5​	1​	6​	3.25​
Account#2​	1​	1​	1​	1​	1​
					4.25​

Entity 3​	Week1​	Week2​	Week3​	Week4​	Average​
Account#1​	1​	5​	1​	6​	3.25​
Account#2​	1​	1​	1​	1​	1​
					4.25​
Entity 4​	Week1​	Week2​	Week3​	Week4​	Average​
Account#1​	1​	5​	1​	6​	3.25​
Account#2​	1​	1​	1​	1​	1​
					4.25​

 

[Lee J]

Hi Bahaa,

I have a solution for you it will require you setting up the sheet as you have laid out above for Entity 1 and using the following formula in B2

=SUM(SUMIF(INDIRECT("'"&$A1&"'!A1:A10"),$A2,INDIRECT("'"&$A1&"'!B1:B10")))

What this does is pick up the Entity 1 reference which I have assumed you have used as a sheet name and then matches Account#1 on that sheet getting the data from column B, for account#2 copy the formula down you will have to replace $A2 with $A1 - you could absolute reference it but doing it that way will cause you issues with part 3.

For week 2 change the B1:B10 to C1:C10 and so on for the other weeks.

PART 3 you can then copy the section down your sheet changing just the Entity # to update the values

See the image below hope it helps

 

[Bahaa]

Hi Bahaa,

I have a solution for you it will require you setting up the sheet as you have laid out above for Entity 1 and using the following formula in B2

=SUM(SUMIF(INDIRECT("'"&$A1&"'!A1:A10"),$A2,INDIRECT("'"&$A1&"'!B1:B10")))

What this does is pick up the Entity 1 reference which I have assumed you have used as a sheet name and then matches Account#1 on that sheet getting the data from column B, for account#2 copy the formula down you will have to replace $A2 with $A1 - you could absolute reference it but doing it that way will cause you issues with part 3.

For week 2 change the B1:B10 to C1:C10 and so on for the other weeks.

PART 3 you can then copy the section down your sheet changing just the Entity # to update the values

See the image below hope it helps

Thank you Lee but the issue i have 460 sheets so i need a macro that can read all data from all sheets and consolidate it under one sheet in the above format

 

[mohadin]

TRy

Sub test()
    Dim a As Variant
    Dim i, lr, l As Long
    ReDim a(1 To 460)
    For i = 1 To UBound(a)
        With Sheets("Sheet " & i & " Data")
            a(i) = .Range("A1").CurrentRegion
        End With
    Next
    ThisWorkbook.Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Consolidate sheet"
    With Sheets("Consolidate sheet")
        For i = 1 To UBound(a)
            lr = .Cells(Rows.Count, 1).End(xlUp).Row
            Cells(lr + l, 1).Resize(UBound(a(i)), UBound(a(i), 2)) = a(i)
            l = l + UBound(a(i))
        Next
    End With
End Sub