PDA

View Full Version : How do I get Autofilter to select the next criteria?



Star Key
08-28-2007, 11:00 PM
I'm just a beginner at this and am not sure how to proceed. What I need is to get the autofilter to automatically select the next criteria, without actually specifying what the criteria is, until the end of the criteria list. i.e. in the example below, instead of Criteria1:=B, Criteria1:=C, etc, as the next criteria, I would like it to just go on to the next criteria in the list until the list runs out. This is what i want to use it in.
Sub Autofilter_progressive()

Selection.AutoFilter Field:=10, Criteria1:="A'
Application.Goto Reference:="R1C5"
Selection.End(xlDown).Select
Range("E18:I18").Select
Selection.Copy
ActiveSheet.Previous.Select
Application.Goto Reference:="R2C2"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False
Range("B3").Select
ActiveSheet.Next.Select
Selection.AutoFilter Field:=10, Criteria1:="B"
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Previous.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False
Range("B4").Select
ActiveSheet.Next.Select
Selection.AutoFilter Field:=10, Criteria1:="C"
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Previous.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False</p> Range(&quot;B5&quot;).Select
ActiveSheet.Next.Select
Selection.AutoFilter Field:=10, Criteria1:=&quot;D&quot;
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Previous.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False
Range(&quot;B6&quot;).Select
ActiveSheet.Next.Select
End Sub
Looking forward to the solution.Thanks

shades
09-01-2007, 08:22 AM
Howdy, and welcome to the board.

Just a question: Is this specific to the Mac version of Excel, or general Excel? The reason I ask, is that if it is the latter, I would move it to the Excel forum for broader exposure and better chance of receiving answers.

Star Key
09-02-2007, 05:15 PM
Thanks Shades. This is Mac specific.

tpoynton
09-02-2007, 06:35 PM
a sample workbook would be helpful, and not to be disrespectful, but I'm not seeing anything mac specific in that code. I'd recommend posting/moving in the regular excel forum. in fact, there are probably folks there who could answer the question without the sample workbook :)

Star Key
09-04-2007, 05:00 PM
tpoynton, while there is nothing mac specific in my code my concern was that the answer would contain code that does not work on my mac, e.g. sendkeys does not work on my mac with the version of excel/vba that I have. But I guess it won't hurt to put it out there. What's the best way to move to the regular excel forum?

tpoynton
09-04-2007, 06:03 PM
I'd recommend posting a new post in the regular Excel forum, with a link to this post. mention that you are using a Mac, and the Excel version you are using. if you come up with a solution there that doesnt work on the Mac, then come back here with the specifics; i've always been able to find a workaround, but I have to say that the vast majority of VBA works on both Mac and PC's. The Mac VBA help does a pretty good job of explaining what works and doesnt in Mac, too.

Also, to help improve the readability of your posts, use the VBA tags

I think a bigger issue than mac-specific code is the fact that Mac VBA tends to be a version behind Windows.

sarmuveera
12-15-2018, 04:09 AM
Just a question: Is this specific to the Mac version of Excel, or general Excel? The reason I ask, is that if it is the latter, I would move it to the Excel forum for broader exposure and better chance of receiving answers.
Flyer maker (https://itunes.apple.com/us/app/flyer-templates-for-pages/id1110289106)

manomehar
01-10-2019, 12:44 AM
Hi, I am manomehar
I read your thread If you utilize Microsoft surpass on your mac, you'll be able to save the spreadsheets you produce and open them in Numbers, Apple's computer programme app. it is a handy feature to use. This is the very best thing.