Help: Filter to Email results

[Walshie1987]

Hi,

I've been looking all over the internet and can't find what I'm looking for.....So I thought i'd try here No doubt someone can help!

Basically I have a spreadsheet, see attached. I want to click a button and a filter to be ran, the filter will filter through the list of names (column A), then email these results to a specific email address (column E). Then run the filter for the next name and so on.

Any ideas?

Cheers
Chris

 

[Chance1234]

well what have you tried so far ? #

all the code for what you want to do is either in the FAQ's or has been posted in this forum.

Chance,

F, G + 3MSTG

 

[Walshie1987]

Basically I'm doing it manually at the minute, but know there's got to be a way of doing it automatically. Just spent quite a while looking in the FAQs but can't seem to see anything (or part of anything) I could use.

Anyone point me to an FAQ or Post please?

Chris

 

[Chance1234]

Walshie,

Break it down into components then search on those components

ie

1, you need to move/loop through your range

2, you are going to need to get your range in an email (or attachment maybe?)

3, you are then going to need to email it

the last has been covered many times on here.

Thats effecticly three chunks of code you need to write all of which with a little investigation should be easy to track down.



Chance,

F, G + 3MSTG

 

[Walshie1987]

Thanks, see what i can come up with

 

[Chance1234]

not trying to bust your balls here, but please remember we are not here to write your code for you. We are here to help and offer any tips or advice.

When you get some code together post it up and we can then take it from there.



Chance,

F, G + 3MSTG

 

[Walshie1987]

Getting there. Just one more little query, I have my main spreadsheet with all my data on i want to filter, and on another sheet (Sheet1) I have a list of all the different filter criteria.

In my code i want to say:

For n = 2 to 10

Filtercriteria = "Sheet1!" & cells(n,2)

Selection.AutoFilter field:=8, Criteria1:=FilterCriteria

etc.

But i don't know how to specify the cells on another sheet other then the active one.

Hope you get what I mean

Chris

 

[xlbo]

Filtercriteria = sheets("Sheet1").cells(n,2)


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Walshie1987]

Cheers xlbo

 

[Chance1234]

HAve you looked at advancedfilter at all ?

http://www.dailydoseofexcel.com/archives/2004/11/22/advanced-filtering-in-vba/

Chance,

F, G + 3MSTG

 

[Chance1234]

hit enter too soon. Was going to add there is copytorange availiable in advanced filter which might be what you are after

This is the only example i can find to hand

Sheet2.Range("c_datawithheads").AdvancedFilter _
                Action:=xlFilterCopy, CriteriaRange:=Range("filtercriteria"), CopyToRange:=Sheet3.Range("Z1")
                'Name the newly created filtered table
Sheets("filter").Range("Z1").CurrentRegion.Offset(1, 0).Name = "Filtered_Data"

Chance,

F, G + 3MSTG

 

[Walshie1987]

This is the code I have:

Sub Filter()

Dim FilterCriteria
Dim CurrentFileName As String
Dim NewFileName As String
Dim foldername As String


For n = 2 To Application.CountA (Sheets("Sheet1").Range("A:A"))


foldername = "C:\Documents and Settings\cwalsh\My Documents\storage\Testing\" & "Reports Dated " & Format(Date, "dd-mm-yyyy") & "\"
CurrentFileName = ActiveWorkbook.Name
savefile = foldername & Workbooks(CurrentFileName).Sheets("Sheet1").Cells(n, 1).Value & " " & Format(Date, "dd-mm-yyyy") & ".xls"

Range("A1:AF1000").Select

On Error Resume Next
MkDir foldername
On Error GoTo 0
Selection.AutoFilter

FilterCriteria = Sheets("Sheet1").Cells(n, 1)

Selection.AutoFilter field:=8, Criteria1:=FilterCriteria

Selection.SpecialCells(xlCellTypeVisible).Select

Selection.Copy

Workbooks.Add Template:="Workbook"

Range("A1").Select

ActiveSheet.Paste

ActiveSheet.SaveAs Filename:=savefile

ActiveWorkbook.SendMail _
Recipients:=Workbooks(CurrentFileName).Sheets("Sheet1").Cells(n, 2), _
Subject:="Chris' Test " & Format(Date, "dd/mmm/yy")


ActiveWorkbook.Close


Application.CutCopyMode = False

Workbooks(CurrentFileName).Activate

Selection.AutoFilter field:=8

Selection.AutoFilter

Range("A1").Select
End If

Next n

MsgBox "All Files Seperated " & vbCrLf & "Files stored in: " & foldername, vbOKOnly, "Thanks"

End Sub

I'm a bit stuck now, the code works fine, i just want to advance a little by saying "If there are Filter results then skip the saving file bit, close the window then move onto the next filter criteria

Can anyone help please

 

[Walshie1987]

Sorry I meant to put if there AREN'T any filter results then....

Cheers
Chris

 

[xlbo]

To test for filter results:

If Range("Sheet1!_FilterDatabase").SpecialCells(xlCellTypeVisible).Rows.Count > 1 then
  msgbox "There are filter results"
else
  msgbox "No filter results"
end if

Where Sheet1 is the name of your worksheet which has the filter applied

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question