Hi everybody,

I've been scratching my head very hard to solve this problem. First I
will give you the code, then I will explain my problem.

1/ the code - the objective is to take the values from 3 differents
columns of the sheet "data" (column 13 (M), 32(AF), 33(AG)) and put them
in 1 column of the sheet agenda.

Dim rng as range

With Sheets("data") 'CA
Set rng = .Range(.Cells(1, 13), .Cells(Rows.Count, 13).End(xlUp))
End With

rng.Advancedfilter Action:=xlFilterCopy, _
CopyToRange:=Sheets("agenda").Range("A1"), Unique:=True

MsgBox "ok CA"
cLastRow = Worksheets("agenda").Cells(Rows.Count, "A").End(xlUp).Row
MsgBox cLastRow

With Sheets("data") 'Assigned investigator
Set rng = .Range(.Cells(2, 32), .Cells(Rows.Count, 32).End(xlUp)) 'i put
cell 2 not to catch label
End With

rng.Advancedfilter Action:=xlFilterCopy, _
CopyToRange:=Sheets("agenda").Range("A" & cLastRow + 1), Unique:=True

MsgBox "ok assigned"
cLastRow = Worksheets("agenda").Cells(Rows.Count, "A").End(xlUp).Row
MsgBox cLastRow

With Sheets("data") 'Actual investigator
Set rng = .Range(.Cells(2, 33), .Cells(Rows.Count, 33).End(xlUp))
End With

'If rng.Cells.Count < 2 Then Exit Sub

rng.Advancedfilter Action:=xlFilterCopy, _
CopyToRange:=Sheets("agenda").Range("A" & cLastRow + 1), Unique:=True

MsgBox "ok actual"
cLastRow = Worksheets("agenda").Cells(Rows.Count, "A").End(xlUp).Row
MsgBox cLastRow

With Sheets("agenda") 'keep only the unique values
Set rng = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
End With

rng.Advancedfilter Action:=xlFilterCopy, _
CopyToRange:=Sheets("agenda").Range("B1"), Unique:=True

MsgBox "ok unique"
cLastRow = Worksheets("agenda").Cells(Rows.Count, "B").End(xlUp).Row
MsgBox cLastRow

2/ My problem : this code works fine except for one thing : I can't
paste the 3rd column of my data. There simply nothing that is paste in
my agenda sheet!!

Any ideas why/ or ideas to fix this problem???

You'll remark that the code to paste the 3 columns from the "data" sheet
is more or less 3 time the same thing so I just don't understand why it
doesn't work...

Any help will apreciated very, very much...

Vincent

Philadelphia, USA.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!