Filter and copy macro

  • Thread starter Thread starter Zak
  • Start date Start date
Z

Zak

Hi,

I have had numerous attempts at making a macro filter and then copy but
having no luck.

-How do i tell a macro to filter sheet "A" with criteria "Today" in column E
-Then copy columns A,B,C,E,F,G,J,M,V,AE,AF,AU.. (with filter applied) into a
new sheet in the same workbook and call it "Done" (sheet to be created).
-The filter in sheet "A" should be un-applied now.. so it shows everything
again.

Can i also get the macro to put this spreadsheet into an email with a
specified mailing list and a certain mailbox rather than straight from my
inbox.. and have a set template messgae in the email aswell?

Thanks in advance.
 
Zak,

Try this

Sub stance()
Sheets("Sheet1").Select
Dim myrange, copyrange As Range
what = "Today"
lastrow = Cells(Cells.Rows.Count, "E").End(xlUp).Row
Set myrange = Range("E1:E" & lastrow)
For Each c In myrange
If c.Value = what Then
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next
copyrange.Copy
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Done"
ActiveSheet.Paste
With ActiveSheet
.Range("D:D,H:I,K:L,N:U,W:AD,AG:AT,AV:IV").Delete

End With
End Sub


Mike
 
Hi,

The maco works ok but it doesnt copy across the column headers and also i
noticed the columns you had stated werent the ones i wanted so when i changed
it to the ones i did want it just reports error, have i put them correctly?

..Range("A:B,C:F,G:G:,J:J,M:M,V:V,AE:AE").Delete

Also, in a code like the above am i able to put the columns in any order?
doesnt have to follow the A,B,C sequence?

thank u for promt reply.
 
Zak,

You never said you wanted column headers, you said you wanted columns
A,B,C,E,F,G,J,M,V,AE,AF,AU where a condition in column E was met and the
macro does that.

What it actually does is copy all the row and then deletes the columns you
dont want. If you want headers which I assume are in row 1 then try this

Sub stance()
Sheets("Sheet1").Select
Dim myrange, copyrange As Range
Set copyrange = Rows(1).EntireRow
what = "Today"
lastrow = Cells(Cells.Rows.Count, "E").End(xlUp).Row
Set myrange = Range("E1:E" & lastrow)
For Each c In myrange
If c.Value = what Then

Set copyrange = Union(copyrange, c.EntireRow)
End If
Next
copyrange.Copy
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Done"
ActiveSheet.Paste
With ActiveSheet
.Range("D:D,H:I,K:L,N:U,W:AD,AG:AT,AV:IV").Delete
End With
End Sub


Mike
 
Thats has worked fine now but can i ask why you wrote the code like:

..Range("D:D,H:I,K:L,N:U,W:AD,AG:AT,AV:IV").Delete

instead of:

..Range("A:B,C:F,G:G:,J:J,M:M,V:V,AE:AE").Delete

i would just like to know the logic behind it so that i can alter it later
when required. At the moment it is copying one extra column that i dont need
and i am unable to alter the code because i dont know how! it copies column
AF when i dont need it. and u said it copies all but deletes unwanted ones -
how does code know which ones to delete? i cant see where in code you
specified.

thanks a lot.
 
Zak,

How a range is created in VBA is a matter of preference and there are
several ways and I'm sure others will say their way is better but there we
are. In this particular case you said you wanted columns
A,B,C,E,F,G,J,M,V,AE,AF,AU
Click to expand...

to be copied over. Because I was copying to a new sheet then I also new I
was OK to delete copied data and so I decided to copy all columns and delete
the ones you didn't want using

..Range("D:D,H:I,K:L,N:U,W:AD,AG:AT,AV:IV").Delete

As you have noted I do leave column AF in because in your first post that
was what you asked but if you want to remove AF then add it to the delete
range like this

..Range("D:D,H:I,K:L,N:U,W:AD,AF:AT,AV:IV").Delete

If you look closely at the delete range you will see that sometimes there is
a single column between the comma delimiters and sometimes there are several.
Studying this line carefully should help you inderstand how to change the
columns you keep and delete.

Mike
 
Hi,

Thats was very well explained, i now understand what you mean..

Thanks a lot for this.

I've also posted something on date parameters, are you able to help?

thanks again.
 
Back
Top