VBA Noob napisal(a):
Hi all,

I've got a spreadsheet which I want to e-mail extracts out to people in
a list.

I was thinking of running a advance filter on the field but not sure
how to loop through all the names from a data validation list in A5
plus only e-mail the ones who have a entry great than zero (Formula i'm
using for filter currently - SUBTOTAL(3,A14:A133).

Below is the code I've got so far which will e-mail one sheet at a
time.


Code:
--------------------

Sub Auto_E_Mail()

On Error Resume Next

Dim Oldsheet As Worksheet
Set Oldsheet = ActiveSheet

Application.ScreenUpdating = False
Range("A5").Select
ActiveSheet.Unprotect
Columns("S:AI").Select
Selection.EntireColumn.Hidden = False
Range("A13").Select
Selection.CurrentRegion.Select
Sheets("Control panel").Select
Sheets.Add
ActiveSheet.Move Befo=Sheets(1)
Sheets("Control panel").Select
Selection.Copy
Sheets(1).Select
Range("A13").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Control panel").Select
Range("A10:AW12").Copy
Sheets(1).Select
Range("A10").Select
ActiveSheet.Paste
Columns("T:AH").Select
Selection.EntireColumn.Hidden = True
ActiveSheet.Name = Range("A2").Value
Range("A2").Select
Sheets("Control panel").Select
Columns("T:AH").Select
Selection.EntireColumn.Hidden = True
Range("A13").Select
Sheets(1).Move

Set wb = ActiveWorkbook
With wb
.SaveAs ActiveSheet.Range("A2").Value & ".xls"
End With
Application.Dialogs(xlDialogSendMail).Show
'ActiveWindow.Close
With wb
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With

Oldsheet.Select
Application.ScreenUpdating = True
End Sub

--------------------


Thanks

VBA Noob


for each cell in range(your list range)
if your condition is met then
your aboce code
end if
next

mcg