asked on

New sheet with one of each name

Hiya all

I have a sheet that has lots of the same values in one coloum. I want to be able to create a new sheet that show a single coloum with only one of each of the items:

So this is and example of the sheet i have:

doors
chairs
planks
chairs
chairs
planks
chairs
planks
chairs
planks
chairs
planks

And on the next sheet i want it to ony show one of each like this:

doors
chairs
planks

SiddharthRout

If you are using MS Excel 2007 onwards then this will help you. Just rename the Sheets and Column names as applicable.

Sub Sample()
    Sheets("Sheet1").Columns("A:A").Copy Sheets("2").Columns("A:A")
    Application.CutCopyMode = False
    Sheets("Sheet2").Range("$A$1:$A$12").RemoveDuplicates Columns:=1, Header:=xlNo
End Sub

Open in new window

Sid

willa666

ASKER

hi SiddharthRout

I am using excel 2011 on the mac so it should support your solution. But i am used to using formuals and am not sure how i would use you soultion.

SiddharthRout

Paste the code in a module in the VBA editor and simply run the macro "Sample"

Sid

willa666

ASKER

So i managed to create the macro and when i run it i get this error:

Run-Time error '9':

Subscript out of range

Open in new window

SiddharthRout

Ok. Upload your exact file. Let me amend it upload it for you :)

Sid

willa666

ASKER

Thank you matey
Workbook1.xlsm

ASKER CERTIFIED SOLUTION

SiddharthRout

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

willa666

ASKER

:) that works great thank you

SiddharthRout

You are welcome :)

Sid