I just wanted to say thanks. I did get the code to work.



"Don Guillett" wrote:

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DaveH" wrote in message
...
That is working with the exception that it seems to not pick up the last
couple of names in the list. The only differences I can see between the
cells
are that the names consist of two words rather than one single word. Would
that keep the macro from continuing?
"Don Guillett" wrote:


Your OP said col A for names and col B for numbers. That is the logical
way
to do it. Why did you change. Anyway, modify to this
Option Explicit
Sub sumeachsheet()
Dim n As Range
Dim ms As Long
Dim ws As Worksheet
Dim c, firstaddress

For Each n In Range _
("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row)
ms = 0
For Each ws In Worksheets
If ws.Name < ActiveSheet.Name Then
With ws.Columns(3)
Set c = .Find(n, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
firstaddress = c.Address
Do
ms = ms + c.Offset(,-1)
Set c = .FindNext(c)
Loop While Not c Is Nothing And _
c.Address < firstaddress
End If
End With
End If
Next ws
n.Offset(, 1) = ms
Next n
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DaveH" wrote in message
...
Don,
Thanks for the quick reply.
I could not get the macro to work correctly but I did have to move the
info
in the sheets around for formatting.
The names are in column C of each sheet and the data is in column B.
There
is other information in those columns that may be interfering with the
macro.
I have a sheet with the names Im looking for in column A if that would
help.


"Don Guillett" wrote:

Use this macro assigned to a button or shape

Option Explicit
Sub sumeachsheet()
Dim n As Range
Dim ms As Long
Dim ws As Worksheet
Dim c, firstaddress

For Each n In Range _
("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row)
ms = 0
For Each ws In Worksheets
If ws.Name < ActiveSheet.Name Then
With ws.Columns(1)
Set c = .Find(n, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
firstaddress = c.Address
Do
ms = ms + c.Offset(, 1)
Set c = .FindNext(c)
Loop While Not c Is Nothing And _
c.Address < firstaddress
End If
End With
End If
Next ws
n.Offset(, 1) = ms
Next n
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DaveH" wrote in message
...
I need to go thru several worksheets and look for a specific set of
text
in
row A, and add up the values next to the text in row B and save that
into
a
group of cells in the current sheet.

I.e. Search criteria Dog, Cat, Bird, Snake.

Worksheet 1
Dog 12
Cat 1
Bird 3

Worksheet 2
Cat 1
Snake 2

Worksheet 3
Dog 1
Snake 10


Output to cells in current worksheet
Dog 13
Cat 2
Bird 3
Snake 12
Any help would be greatly appreciated.
Dave

The archives want to see your final solution.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DaveH" wrote in message
...
I just wanted to say thanks. I did get the code to work.



"Don Guillett" wrote:

If desired, send your file to my address below along with this msg
and
a clear explanation of what you want and before/after examples.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DaveH" wrote in message
...
That is working with the exception that it seems to not pick up the
last
couple of names in the list. The only differences I can see between the
cells
are that the names consist of two words rather than one single word.
Would
that keep the macro from continuing?
"Don Guillett" wrote:


Your OP said col A for names and col B for numbers. That is the
logical
way
to do it. Why did you change. Anyway, modify to this
Option Explicit
Sub sumeachsheet()
Dim n As Range
Dim ms As Long
Dim ws As Worksheet
Dim c, firstaddress

For Each n In Range _
("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row)
ms = 0
For Each ws In Worksheets
If ws.Name < ActiveSheet.Name Then
With ws.Columns(3)
Set c = .Find(n, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
firstaddress = c.Address
Do
ms = ms + c.Offset(,-1)
Set c = .FindNext(c)
Loop While Not c Is Nothing And _
c.Address < firstaddress
End If
End With
End If
Next ws
n.Offset(, 1) = ms
Next n
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DaveH" wrote in message
...
Don,
Thanks for the quick reply.
I could not get the macro to work correctly but I did have to move
the
info
in the sheets around for formatting.
The names are in column C of each sheet and the data is in column B.
There
is other information in those columns that may be interfering with
the
macro.
I have a sheet with the names Im looking for in column A if that
would
help.


"Don Guillett" wrote:

Use this macro assigned to a button or shape

Option Explicit
Sub sumeachsheet()
Dim n As Range
Dim ms As Long
Dim ws As Worksheet
Dim c, firstaddress

For Each n In Range _
("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row)
ms = 0
For Each ws In Worksheets
If ws.Name < ActiveSheet.Name Then
With ws.Columns(1)
Set c = .Find(n, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
firstaddress = c.Address
Do
ms = ms + c.Offset(, 1)
Set c = .FindNext(c)
Loop While Not c Is Nothing And _
c.Address < firstaddress
End If
End With
End If
Next ws
n.Offset(, 1) = ms
Next n
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DaveH" wrote in message
...
I need to go thru several worksheets and look for a specific set
of
text
in
row A, and add up the values next to the text in row B and save
that
into
a
group of cells in the current sheet.

I.e. Search criteria Dog, Cat, Bird, Snake.

Worksheet 1
Dog 12
Cat 1
Bird 3

Worksheet 2
Cat 1
Snake 2

Worksheet 3
Dog 1
Snake 10


Output to cells in current worksheet
Dog 13
Cat 2
Bird 3
Snake 12
Any help would be greatly appreciated.
Dave