Code for Copy and Sort?

[Carp]

I need some code.

I want to copy and sort numbers from Sheet1 column B to
Sheet4 column A. I don’t want to affect Sheet1 column B
and I want duplicates removed.

I’ve been through 9 pages of posts and I can’t find anything.

Please don’t tell me to record a macro. I want code that I
can read, understand and alter.

 

[Legacy 21301]

Please don’t tell me to record a macro.

Why not?

 

[SIXTH SENSE]

hi!
i guess you can work from here to fit your range.
this will copy all the unique value in sheet 1 column A then paste it in column a of sheet2 and sort

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Macro1()
<SPAN style="color:#00007F">Dim</SPAN> X()
Worksheets(1).Select
    Range("A:a").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Columns _
        ("A:A"), CopyToRange:=Range("B1"), Unique:=<SPAN style="color:#00007F">True</SPAN>
     X = Range("B1:B" & Range("B65536").End(xlUp).Row)
    Range("B1" & ":B65536").ClearContents
    Worksheets(2).Select
    Range(Cells(1, 1), Cells(UBound(X), 1)) = X
       Range("A1").Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Carp]

Hi SIXTH SENSE, thanks for your quick reply.

I’ve been searching here for some 5 hours now. I can’t believe
no one else has had this problem.

I can’t get your code to do anything. I’m beginning to feel
rather stupid…..

Hi Ponsy Nob.

The reason I don’t want to record a macro is kind of political
I guess… overhead… I want to be able to read the code…
and change it if I want to… I don’t trust code I can’t read and
so on.

 

[fairwinds]

The reason I don’t want to record a macro is kind of political
I guess… overhead… I want to be able to read the code…
and change it if I want to… I don’t trust code I can’t read and
so on.

If you record a macro, you can view the code and change it. Just open the visual basic editor.

 

[SIXTH SENSE]

Hi SIXTH SENSE, thanks for your quick reply.

I’ve been searching here for some 5 hours now. I can’t believe
no one else has had this problem.

I can’t get your code to do anything. I’m beginning to feel
rather stupid…..

Hi Ponsy Nob.

The reason I don’t want to record a macro is kind of political
I guess… overhead… I want to be able to read the code…
and change it if I want to… I don’t trust code I can’t read and
so on.

try putting your data on sheet1, column A then run the macro.

Book1
	A	B	C	D
1	MYDATA		1
2	2
3	3
4	6
5	4
6	2
7	5
8	2
9	1
10	5
11	7
12	1445
Sheet1

the result in sheet2 will be

Book1
	A	B	C	D
1	MYDATA
2	1
3	2
4	3
5	4
6	5
7	6
8	7
9	1445
Sheet2

 

[Carp]

Thanks SIXTH SENSE, but I can’t get it to work. Are you
using Excel 2003 too?

I guess I should cave in and record a macro.

Can any of you guys tell me how to remove the duplicates
when I record the macro?

Thanks.

 

[SIXTH SENSE]

hhhhhhhhhmmmmmmm.
if it does not work then do it your own way.
one way is to use advance filter.
Data>advancefilter
then check unique values only.
you will see simillar code above.
good luck!

 

[Carp]

Ok SS. It should be interesting to see if I get the same code
as you. I’ll let you know.

 

[Carp]

SS, look at the code my macro recording generated... lol ... the advanced
filter wouldn't let me paste to another sheet... ? ... I'm going home from
work now. I'll be back here in a couple of hours.

Sub Makro7()

    Range("A2:A32").Select
    Selection.Copy
    Range("C2").Select
    ActiveSheet.Paste
    Range("C1:C32").Select
    Range("C32").Activate
    Application.CutCopyMode = False
    Range("C1:C32").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    
End Sub