Prev: Help with error on PasteSpecial
Next: Create CSV
From: fishy on 30 Apr 2010 06:20
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.
From: Jacob Skaria on 30 Apr 2010 07:04
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.
From: Javed on 30 Apr 2010 07:09
On Apr 30, 3:20 pm, fishy <fi...(a)discussions.microsoft.com> 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.

Follow the following codes:
******************************
Dim Rng as Range,

For each Rng in Worksheets("Control").range("c8:c34")
Rng.copy
Worksheets("Control").Range("c6").pastespecial
paste:=xlpastevalues
Call FilterDistricts
Next Rng
********************************




 | 
Pages: 1
Prev: Help with error on PasteSpecial
Next: Create CSV