Creating and using an array

[John Ortt]

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" & 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

 

[Bob Phillips]

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]

Far better,

Thanks Bob.