Summing over multiple identical sheets for criteria entered in same cell on each sheet

[namreh]

Hi.

I Have 50 worksheets all identical accept the the first and second, my summary sheets.
I want to sum C12 on sheet3 to sheet50 if the value in B6 is 1.
B6 on the sheets differ from sheet to sheet and is inserted there with Vlookup from a linked file.
Explained in English it sound so easy as:
Sum all the values in C12 over all the sheets to this cell where
I am entering this formula only for the sheets with value of 1 in B6.

Sorry if this was answered before and I missed it.

Thank you.

 

[mrshl9898]

I think I follow you now, go through tabs 3:50 and sum the same cell, then return that to Sheet1?

Perhaps:

Sub addvalues()

Dim ws As Worksheet
Dim myvar As Long
Dim colnum As Long

myvar = 0
colnum = 3


Do Until colnum = 10
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Sheet1" Or ws.Name <> "Sheet2" Then ''change to sheet names not to count
        If ws.Range("B6") = 1 Then
            myvar = myvar + ws.Cells(12, colnum)
        End If
    End If
Next ws
Sheets("Sheet1").Cells(12, colnum) = myvar
colnum = colnum + 1
Loop

End Sub

Are you always looking for 1? If you want to match the value in Sheet1 B6 you can use:

Sub addvalues()

Dim ws As Worksheet
Dim myvar As Long
Dim colnum As Long

myvar = 0
colnum = 3


Do Until colnum = 10
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Sheet1" Or ws.Name <> "Sheet2" Then ''change to sheet names not to count
        If ws.Range("B6") = Sheets("Sheet1").Range("B6") Then
            myvar = myvar + ws.Cells(12, colnum)
        End If
    End If
Next ws
Sheets("Sheet1").Cells(12, colnum) = myvar
colnum = colnum + 1
Loop

End Sub

 

[namreh]

You do follow me now.
Go through tabs 3:50 and sum the same cell, then return that to Sheet1?
I like your second code. On sheet1 b6 enter what I want to sum for.
I am starting to understand the coding.
For testing purposes, I have entered the value of 2 in all sheets 3:50
Only 2 sheets have a 1 in B6
That means that colnum 3 to 9 should all evaluate to 4
Not so, it goes 4,8,12,16,20,24
We have only done row 12
It must go to row 17.

Thanks for your help.

 

[namreh]

I got it working. During the 90s I did a lot of Dbase4 programming.
Principle is the same so I am learning.
I want to close this thread now but for one problem (sure more will emerge)
If you run the code without erasing data on sheet1, the next run will add the value on sheet1 to the sum data.
So say C12 evaluate to 10
next run 20
next run 30....... etc.
Can I just add some code? and how?
This is what I have done to your code.

Sub addvalues1()

Dim ws As Worksheet
Dim myvar As Long
Dim colnum As Long
Dim rownum As Long

myvar = 0
colnum = 3
rownum = 12

Do Until rownum = 18
Do Until colnum = 10
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Sheet1" Or ws.Name <> "Sheet2" Then ''change to sheet names not to count
If ws.Range("B6") = Sheets("Sheet1").Range("B6") Then
myvar = myvar + ws.Cells(rownum, colnum)
End If
End If
Next ws

Sheets("Sheet1").Cells(rownum, colnum) = myvar
myvar = 0
colnum = colnum + 1


Loop
colnum = 3 ' to go down to next row to sum
rownum = rownum + 1
Loop


End Sub