Creating and using an array

Hi everyone,

I have a section of code which repeats with minimal changes ( 2 items per
repeat)

These two items represent the original column copied and the column it is
copying to.

Is there any way I could simplify my code using an array and looping through
it?

Thanks for the help,

John


My original code is as follows:

Worksheets(2).Select
Columns("X:X").Clear
Worksheets(2).Range("P1:P" & lastrow).AdvancedFilter
Action:=xlFilterCopy, _
CopyToRange:=Range("X1"), Unique:=True
Columns("X:X").Select
Selection.Sort Key1:=Range("X2"), Order1:=xlAscending, Header:=xlYes
Selection.Copy
Sheets("Menu").Select
Columns("I:I").PasteSpecial Paste:=xlPasteValues

Worksheets(2).Select
Columns("X:X").Clear
Worksheets(2).Range("A1:A" & lastrow).Select
Selection.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("X1"), Unique:=True
Columns("X:X").Select
Selection.Sort Key1:=Range("X2"), Order1:=xlAscending, Header:=xlYes
Selection.Copy
Sheets("Menu").Select
Columns("J:J").PasteSpecial Paste:=xlPasteValues

Is this any better for you

CopyData Worksheets(2).Range("P1"), Worksheets("Menu").Range("I:I")
CopyData Worksheets(2).Range("A1"), Worksheets("Menu").Range("J:J")

'...

Sub CopyData(StartCell As Range, Target As Range)
With Worksheets(2)
.Select
.Columns("X:X").Clear
StartCell.Resize(lastrow).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("X1"), _
Unique:=True
With .Columns("X:X")
.Sort Key1:=Range("X2"), Order1:=xlAscending, Header:=xlYes
.Copy
End If
End With
Sheets("Menu").Select
Target.PasteSpecial Paste:=xlPasteValues
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"John Ortt" <[email protected]> wrote in message
news:[email protected]...
> Hi everyone,
>
> I have a section of code which repeats with minimal changes ( 2 items per
> repeat)
>
> These two items represent the original column copied and the column it is
> copying to.
>
> Is there any way I could simplify my code using an array and looping
through
> it?
>
> Thanks for the help,
>
> John
>
>
> My original code is as follows:
>
> Worksheets(2).Select
> Columns("X:X").Clear
> Worksheets(2).Range("P1:P" & lastrow).AdvancedFilter
> Action:=xlFilterCopy, _
> CopyToRange:=Range("X1"), Unique:=True
> Columns("X:X").Select
> Selection.Sort Key1:=Range("X2"), Order1:=xlAscending, Header:=xlYes
> Selection.Copy
> Sheets("Menu").Select
> Columns("I:I").PasteSpecial Paste:=xlPasteValues
>
> Worksheets(2).Select
> Columns("X:X").Clear
> Worksheets(2).Range("A1:A" & lastrow).Select
> Selection.AdvancedFilter Action:=xlFilterCopy, _
> CopyToRange:=Range("X1"), Unique:=True
> Columns("X:X").Select
> Selection.Sort Key1:=Range("X2"), Order1:=xlAscending, Header:=xlYes
> Selection.Copy
> Sheets("Menu").Select
> Columns("J:J").PasteSpecial Paste:=xlPasteValues
>
>