Assume that the value is to the right of Total and that
"Total" is proper case, only appears once in each sheet
and could be located anywhere on the sheet.

Sub getData()
Dim ws As Range
Dim c As Range
SName = ActiveSheet.Name
lr = Cells(Rows.Count, 1).End(xlUp).Row
For Each ws In Sheets(SName).Range("A2:A" & lr)
If Not ws Is Nothing Then
fAddr = ws.Address
fName = ws.Value
Set c = Worksheets(fName).Cells.Find("Total", LookIn:=xlValues)
If Not c Is Nothing Then
c.Offset(0, 1).Copy Sheets(SName).Range(fAddr).Offset(0, 1)
End If
End If
Next
End Sub

"Chris" wrote:

hi. how do i make a macro that returns the value of the cell next to the word
"total" from all worksheets which are listed in column A? i got to make the
macro that lists all worksheets in that workbooks into a summary worksheet.
Now i would like to find the value of the total amount in each worksheet and
return it to the next column of their corresponding sheet name lists? the
first macro is like this:

Sub auto_open()
Sheets("summary").Activate
x = 1
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Cells(x, 1).Value = ws.Name
x = x + 1
Next ws
Cells(1, 2).Value = x - 1
End Sub