Posted to microsoft.public.excel.programming
|
|
copying unique values to different cell
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
|