CODEWITHSUNDEEP
vbaexcel
Eraa Nova
Eraa Nova

Reputation: 25

VBA Sum between Special Sheets

Hope you are all doing well. Im here because of a question Im trying to solve since this morning and I CANT stand it anymore. That is the context : I have an excel workbook in which I have different sheets containing different business plans for different countries. My goal is to make a consolidated Income statement with the sum of the criteria (COGS, Net profit, Salaries....) in each sell.

Each cell in the A column corresponds to a criterion and I want the numbers to appear in the B column (Total of all companies for each criterion).

Thats my code : Initially it wasnt like this but thats one of my attempting drafts let me explain below why Im trying this format

Private Sub Consolidated_Income_Statement()
Dim Sheet As Worksheet
Dim Consolidated As Worksheet

For Each Sheet In Sheets
    Do While Cells(B2, B152) <> ""
    Consolidated = Sum(Call BPs, B152)
    GoTo Consolidated
Next
End Sub

Sub BPs()
Dim Sheet As Worksheet
For Each Sheet In Sheets
    If Right(Sheet.Name, 50) = "E2016" Then
End Sub

The fact is that I want to sum the cells only of the sheets containing "E2016" and Im trying to create a call button to use it my private sub once I have determined that I only want these E2016 sheets.

And thats how I tried to do it initially 

Private Sub Consolidated_Income_Statement()
Dim Sheet As Worksheet
Dim Consolidated As Worksheet
For Each Sheet In Sheets
    If Right(Sheet.Name, 50) = "E2016" Then
        Do While Cells(B2, B152) <> ""
        Consolidated = Sum('I WANT TO SELECT MY E2016, B152)
        GoTo Consolidated
        Next
End Sub

But nothing is working ! Sorry if these questions are too basic but Im learning VBA on my own for my company since 2 days and Im facing a couple of difficulties ! Thank you very much for your help !

Upvotes: 2

Views: 77

Answers (1)

99moorem
99moorem

Reputation: 1983

Some thing like below?

Private Sub Consolidated_Income_Statement()
Dim Sheet As Worksheet
Dim Consolidated As Integer
For Each Sheet In Sheets
    If Right(Sheet.Name, 5) = "E2016" Then
        Consolidated = Consolidated + Application.WorksheetFunction.Sum(Sheet.Range("B2:B152"))
    End If
Next

MsgBox "Sum is : " & Consolidated

End Sub

Upvotes: 1

Related Questions