Solved: Help with For Each...Next please

[Pete634]

A master workbook contains four cells, F4:F7 on Sheet 2 which contain text relevant to text on 4 other workbooks.

I'm trying to filter each of the other workbooks based on the value of each of the 4 cells seperately, obtaining the numercial value from two subtotal cells at the end of each worksheet which is pasted into the Master workbook before moving onto the next workbook.

I'm OK with doing the pasting bit but I'm struggling with the code for the autofilter. What I have so far is...
[vba]



Dim Range1 as Variant
'................
'....code activates relevant workbook and then selects relevant worksheet

Range1 = thisworkbook.sheets(2).range("F4:F7")

For Each cell in Range1

selection.autofilter Field:=12, Criteria1:=cell.value

'.... more code etc

Next cell
[/vba]

Can anyone help? I'd normally post the workbook but I don't really think this time it's necessary as it's only the above part that I can't get to work and I'm confident someone will know what I clearly don't!

Thanks

[Bob Phillips]

Surely, the problem lies with Selection, that is a static area, wheraeas you seem to wa nt it o be dynamic, a different sheet, different range?

[Pete634]

Sorry, the autofilters are already there on the other worksheets so no issues with them. The problem seems to be with Range1, it just doesn't recognise it. I suppose it needs to be an array but I'm not sure how to code that.

Anyway if I simply delete the "Range1 = ..." line and amend the For Each to
[vba]
For Each cell In ThisWorkbook.Sheets(2).Range("F4:F" & ThisWorkbook.Sheets(2).Range("F65536").End(xlUp).Row)
[/vba]

then problem solved!

I would have liked to know though how to have approached it from the Range1 option as it keeps the For Each part short and sweet.

[Bob Phillips]

When you assign an object variable, you need to Set it, not an implicit Let.