VBA routine Autofilter on multiple cells in Excel 2003

I have some drop down boxes on the front sheet of a workbook that read data from a list of references on the second sheet.

This leaves me with 8 cells of data that I then want to apply (an Autofilter I think is best?) to a 4th sheet called 'Simulator'.

Is it best to use a VBA routine for this (I think it's the only way?) and is it fastest to apply each filter separately or to send all 8 pieces of data across at once?

My Simulator sheet has around 10,000 possible combinations of the 8 pieces of data, so once the first filter is applied things speed up as a large chunk of data has been filtered out.

Sorry if I'm rambling, but haven't dabbled in VBA for about 4 years so just trying to make sure my thought process is correct.

I've taken a look at this thread, and it's similar to what I'm trying to achieve (although I should end up with only one result after all 8 peices of data have been applied to the filter):-

http://www.ozgrid.com/forum/showthread.php?t=85547&page=1

Re: VBA routine Autofilter on multiple cells in Excel 2003

Hmm, rustier than I thought!

My first attempt gives me a Runtime error '1004' and says 'AutoFilter method of Range class failed' when it gets to the line with field=2

ActiveSheet.AutoFilterMode = False
Range("B2:J2").AutoFilter
Range("B2:J2").AutoFilter Field:=1, Criteria1:=Range("B1").Text
Range("B2:J2").AutoFilter Field:=2, Criteria1:=Range("C1").Text
Range("B2:J2").AutoFilter Field:=3, Criteria1:=Range("D1").Text
Range("B2:J2").AutoFilter Field:=4, Criteria1:=Range("E1").Text
Range("B2:J2").AutoFilter Field:=5, Criteria1:=Range("F1").Text
Range("B2:J2").AutoFilter Field:=7, Criteria1:=Range("H1").Value
Range("B2:J2").AutoFilter Field:=8, Criteria1:=Range("I1").Value
Range("B2:J2").AutoFilter Field:=9, Criteria1:=Range("J1").Value

Re: VBA routine Autofilter on multiple cells in Excel 2003

Ok, I've re-arranged and don't get the error but it's not filtering anything!

ActiveSheet.AutoFilterMode = False
Range("B2:J2").AutoFilter
Range("B2:J2").AutoFilter Field:=1, Criteria1:=Range("B1").Text

Range("B2:J2").AutoFilter
Range("B2:J2").AutoFilter Field:=2, Criteria1:=Range("C1").Text

Range("B2:J2").AutoFilter
Range("B2:J2").AutoFilter Field:=3, Criteria1:=Range("D1").Text

Range("B2:J2").AutoFilter
Range("B2:J2").AutoFilter Field:=4, Criteria1:=Range("E1").Text

Range("B2:J2").AutoFilter
Range("B2:J2").AutoFilter Field:=5, Criteria1:=Range("F1").Text

Range("B2:J2").AutoFilter
Range("B2:J2").AutoFilter Field:=7, Criteria1:=Range("H1").Value

Range("B2:J2").AutoFilter
Range("B2:J2").AutoFilter Field:=8, Criteria1:=Range("I1").Value

Range("B2:J2").AutoFilter
Range("B2:J2").AutoFilter Field:=9, Criteria1:=Range("J1").Value

Re: VBA routine Autofilter on multiple cells in Excel 2003

Right, I'm going with this method now:-

http://www.rondebruin.nl/copy5_2.htm

Just need to find a way around the 'parent' part not selecting the right sheet!!