code copy data of certain column from sheet to another doesn't work

[leap out]

hello guys
I have this code it supposes data of column b in sheet1 to column e in sheet2 without any duplicated , but it doesn't happen any thing
so I appreciate if any body provide advice how can fix it .

Sub CopyUnique()
    Dim s1 As Worksheet, FirstEmptyRow As Long, expCol As Long
    Set s1 = ActiveSheet
    With Sheets("sheet1")
        .Range("b:b").Name = "code1"
        expCol = .Range("code1").Column
        FirstEmptyRow = .Cells(.Rows.Count, expCol).End(xlUp).Row + 1
        s1.Range("e2:e100").Copy .Cells(FirstEmptyRow, expCol)
        .Range("code1").RemoveDuplicates Columns:=1, Header:=xlNo
    End With
End Sub

thanks

 

[Fluff]

Your code is doing the exact opposite of what you said it should. Which is correct?

 

[aRandomHelper]

data of column b in sheet1 to column e in sheet2

You mean copy sheet 1 column B to sheet 2 column E?

Your code is doing it wrongly then.

Which is your activesheet? Sheet1 or Sheet2?
Your With block is Sheet1, range code1 is in Sheet1, FirstEmptyRow is of Sheet1.
s1's (Activesheet) column E is being copied to Sheet1.
You're removing duplicates in Sheet1.

 

[leap out]

hi, Fluff
you mean this doesn't copy without duplicated data ?
what about this line

 .Range("code1").RemoveDuplicates Columns:=1, Header:=xlNo

what works for?

 

[leap out]

hi, arandomhelper
my activesheet is sheet2

 

[Fluff]

you mean this doesn't copy without duplicated data ?

No I mean its copying col E to not B not the otherway round.

 

[aRandomHelper]

my activesheet is sheet2

Your code was so confusing you probably confused yourself too.

Try:

Sub CopyUnique()
    Dim s2 As Worksheet, FirstEmptyRow As Long, expCol As Long
    Set s2 = ActiveSheet 'renamed var to "s2" for clarity's sake
    With Sheets("sheet1")
        .Range("b:b").Name = "code1"
        expCol = .Range("code1").Column
        FirstEmptyRow = s2.Cells(.Rows.Count, "E").End(xlUp).Row + 1  'take Sheet2's first empty row in column E, not Sheet1
        .Range("B2:B100").Copy s2.Cells(FirstEmptyRow, "E") 'copy from Sheet1 to Sheet2
        s2.Columns("E").RemoveDuplicates Columns:=1, Header:=xlNo 'remove duplicates in Sheet2
    End With
End Sub

 

[leap out]

@ Fluff it supposes giving me error to find the error which line but the code does not work at all !

 

[Fluff]

Are you trying to copy col E to Col B or the other way round?

 

[leap out]

@Fluff copy col b to col e from sheet1 to sheet2