Selecting more than two items in an AutoFilter

[tangcla]

OK, please interrupt me where I can improve on processes.
As part of my macro, I need to select ten items in my table of data to import into another worksheet.

Is there a way to select more than two if I edit the macro code itself? I tried but it didn't seem to work, by extending the line to include further Criterion:

Selection.AutoFilter Field:=2, Criteria1:="=Fuel Sales", Operator:=xlOr, Criteria2:="=C-Store Sales"

[tangcla]

OK, here's a sample file.

basically, say I want to automate a copy-paste of only "Fuel Sales", "C-Store Sales", "car Wash Sales/GP", "SALES", "GROSS PROFIT" - how would you do it?
I'll need to macro-ize it, too.


My two thoughts:
a) Learn how to do a filter function which can do more than two variables
b) Learn how to specify relative cell references in rows (i.e. "down one row")

[rylo]

Hi

Use the advanced filter. With your example file run the following code.

Sub aaa()
  Dim OutSH As Worksheet, DataSH As Worksheet
  Set OutSH = Sheets("Sheet2")
  Set DataSH = Sheets("Sheet1")
  OutSH.Range("A1:C1").Value = DataSH.Range("A1:C1").Value
  OutSH.Range("E1").Value = DataSH.Range("B1").Value
  OutSH.Range("E2:E6").Value = WorksheetFunction.Transpose(Array("Fuel Sales", "C-Store Sales", "car Wash Sales/GP", "SALES", "GROSS PROFIT"))
  DataSH.Range("A1").CurrentRegion.AdvancedFilter action:=xlFilterCopy, copytorange:=OutSH.Range("A1:C1"), criteriarange:=OutSH.Range("E1:E6")
  
End Sub

rylo

[tangcla]

At the risk of looking noob... how does one use this code you have provided?

Tried using it as a macro and it didn't do anything...

[rylo]

Hi

Open your example file, ALT F11, Insert, Module, paste the code, run the code, then view the output on sheet2.


rylo

[tangcla]

Ohhhh didn't realise it output the files into Sheet2!

Now I gotta see if I can butcher this code into a macro of sorts...

Thanks again, you're my hero rylo