Code for Copy and Sort?

Carp

New Member
Joined
Dec 4, 2003
Messages
48
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. :oops:

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

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
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

New Member
Joined
Dec 4, 2003
Messages
48
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638

ADVERTISEMENT

Carp said:
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

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
Carp said:
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
ABCD
1MYDATA1
22
33
46
54
62
75
82
91
105
117
121445
Sheet1



the result in sheet2 will be
Book1
ABCD
1MYDATA
21
32
43
54
65
76
87
91445
Sheet2
 

Carp

New Member
Joined
Dec 4, 2003
Messages
48

ADVERTISEMENT

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

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
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! :LOL: :LOL: :LOL:
 

Carp

New Member
Joined
Dec 4, 2003
Messages
48
Ok SS. :LOL: It should be interesting to see if I get the same code
as you. I’ll let you know.
 

Carp

New Member
Joined
Dec 4, 2003
Messages
48
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. :biggrin:

Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,329
Messages
5,769,462
Members
425,551
Latest member
yeat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top