Extracting email addresses from worksheets

[SuperFerret]

I have a spreadsheet which I am trying to update, each supplier has their own tab and the contact details are stored on the tab, above all the invoice figures. However, the email addresses can be pretty much anywhere on the worksheet so I can't simply specify a small range to loop through.

Can anyone suggest a way of finding and extracting all the email addresses (anything with an @ symbol) and pasting them into a new sheet?

 

[JackDanIce]

Play around with:

On Error Resume Next 
For i = 1 to worksheets.count
 With Sheets(i)
    Some_variable_of_type_range = .Cells.Find(what:="@", LookIn:=xlValues, lookat:=xlPart)
    Sheets("Summary").Range("A" & i) = Some_variable_of_type_range.Value
  End With
Next i
On Error Goto 0

May need to play around with the On Error part if an email address doesn't exist on the page or can't be found

 

[SuperFerret]

Thanks Jack, I shall have a play with that and see how I get on!

 

[JackDanIce]

Play away, play away!

 

[SuperFerret]

Play away, play away!

Ooh err!

 

[SuperFerret]

Ok, so I've played about with the snippet of code you provided...but I can't get my head around what it's doing and how to amend it to what I need.

I've not done many loops through sheets, so I'm at a loss what to do next.