I have a coloumn(O:O) in sheet1 that has many different values. I want
to copy the unique values to sheet 2 cell C3 to C25. How can I do that
using a VBA codec. I am very new to VB that is why I am asking to help
me.

I saw one which does this but the problem is that it asks for a prompt.
I dont want the prompt but it should directly paste to the cell
refference.

The link is http://www.freevbcode.com/ShowCode.asp?ID=4941

wrote in message
oups.com...
I have a coloumn(O:O) in sheet1 that has many different values. I want
to copy the unique values to sheet 2 cell C3 to C25.

And you know that there are exactly 23 unique values???

--

Vasant

I don't know if this is exactly what you need but you could try
something like this:

Sub UniqueValues()
For Each Cell In Sheets(1).Range("O1:O100")
If Application.WorksheetFunction.CountIf(Range("O1:O6 5536"),
Cell) = 1 Then
Counter = Counter + 1
Sheets(2).Range("C" & Counter + 2) = Cell
End If
Next Cell
End Sub

Hi R4ranjith,

If your intent is to copy unique values, rather than values which only occur
once, then try:

'=======================.
Sub Tester01()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim srcRng As Range
Dim destRng As Range

Set sh1 = ThisWorkbook.Sheets("Sheet1") '<<==== CHANGE
Set sh2 = ThisWorkbook.Sheets("Sheet2") '<<==== CHANGE

Set srcRng = sh1.Range("O1:O200") '<<==== CHANGE
Set destRng = sh2.Range("C3") '<<==== CHANGE

' If the source and destination ranges are _
' not on the same sheet, the advanced filter _
' must be invoked from the destination sheet, so:
sh2.Activate

srcRng.AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=destRng, _
Unique:=True

End Sub
''<<'=======================

This will delete any existing data below the destination cell C3.

If this constitutes a problem, then you can replace:

Set destRng = sh2.Range("C3")

with

Set destRng = sh2.Range("C3C25")

where the range C3:C25 is sufficient to accomodate all potential unique
values in the source range. If this range is too small, the macro will
produce a warning message.


---
Regards,
Norman



wrote in message
oups.com...
I have a coloumn(O:O) in sheet1 that has many different values. I want
to copy the unique values to sheet 2 cell C3 to C25. How can I do that
using a VBA codec. I am very new to VB that is why I am asking to help
me.

I saw one which does this but the problem is that it asks for a prompt.
I dont want the prompt but it should directly paste to the cell
refference.

The link is http://www.freevbcode.com/ShowCode.asp?ID=4941

Hey Norman,

Thank you for that reply. I did exactly the same and it worked
perfectly fine.

Sorry I could not respond quickly :)

Regards,
Ranjith

Norman Jones wrote:
Hi R4ranjith,

If your intent is to copy unique values, rather than values which only occur
once, then try:

'=======================.
Sub Tester01()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim srcRng As Range
Dim destRng As Range

Set sh1 = ThisWorkbook.Sheets("Sheet1") '<<==== CHANGE
Set sh2 = ThisWorkbook.Sheets("Sheet2") '<<==== CHANGE

Set srcRng = sh1.Range("O1:O200") '<<==== CHANGE
Set destRng = sh2.Range("C3") '<<==== CHANGE

' If the source and destination ranges are _
' not on the same sheet, the advanced filter _
' must be invoked from the destination sheet, so:
sh2.Activate

srcRng.AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=destRng, _
Unique:=True

End Sub
''<<'=======================

This will delete any existing data below the destination cell C3.

If this constitutes a problem, then you can replace:

Set destRng = sh2.Range("C3")

with

Set destRng = sh2.Range("C3C25")

where the range C3:C25 is sufficient to accomodate all potential unique
values in the source range. If this range is too small, the macro will
produce a warning message.


---
Regards,
Norman



wrote in message
oups.com...
I have a coloumn(O:O) in sheet1 that has many different values. I want
to copy the unique values to sheet 2 cell C3 to C25. How can I do that
using a VBA codec. I am very new to VB that is why I am asking to help
me.

I saw one which does this but the problem is that it asks for a prompt.
I dont want the prompt but it should directly paste to the cell
refference.

The link is http://www.freevbcode.com/ShowCode.asp?ID=4941