I have a series of queries that uses a range to autofilter.

This works by working through a list in cells C8 to C34.

At present I use the code detailed below but know there must be a way of
excel working through this list more intelligently i.e. not having to name
the cell to copy paste but work through the range in order.

-----------------------------------------------------------------------------------------
Sub EditPt2()

'Filter each district

'District 1
Sheets("Control").Select
Range("C8").Select
Selection.Copy
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Call FilterDistricts

'District 2
Sheets("Control").Select
Range("C9").Select
Selection.Copy
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Call FilterDistricts

etc etc etc.

Not sure what the procedure does; but try the below loop...


Dim lngRow As Long

Sheets("Control").Select
For lngRow = 8 To 34
Range("C6") = Range("C" & lngRow).Value
Call FilterDistricts
Next

--
Jacob (MVP - Excel)


"fishy" wrote:

I have a series of queries that uses a range to autofilter.

This works by working through a list in cells C8 to C34.

At present I use the code detailed below but know there must be a way of
excel working through this list more intelligently i.e. not having to name
the cell to copy paste but work through the range in order.

-----------------------------------------------------------------------------------------
Sub EditPt2()

'Filter each district

'District 1
Sheets("Control").Select
Range("C8").Select
Selection.Copy
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Call FilterDistricts

'District 2
Sheets("Control").Select
Range("C9").Select
Selection.Copy
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Call FilterDistricts

etc etc etc.