Copy unique data over worksheets

[Wim]

I am trying to copy unique ID numbers from a list on a sheet to a column in another sheet.
I got the following code from the net but it give me a run time error and I am not sure why? (Run-time error 9 Subscript out of range)

Sub UniqueListAcrossSheets()

Dim ULR As Long
Dim rSrc As Range

ULR = Worksheets("Sheet5").Cells(Rows.Count, "C").End(xlUp).Row
Set rSrc = Worksheets("Sheet5").Range("C2:C" & ULR)

rSrc.Copy Destination:=Worksheets("Sheet4").Range("A4")

End SuB

Any assistance will be greatly appreciated!

 

[Luke M]

Assuming the sheet names are correct, it should work. The only caveat I see is that there's one Row object not linked to a parent, so if you ran the macro while a ChartSheet was active, it would crash (since charts don't have rows). Does this work any better?

Sub UniqueListAcrossSheets()

Dim ULR As Long
Dim rSrc As Range

With Worksheets("Sheet5")
    ULR = .Cells(.Rows.Count, "C").End(xlUp).Row
    Set rSrc = .Range("C2:C" & ULR)
End With
rSrc.Copy Destination:=Worksheets("Sheet4").Range("A4")

End Sub

 

[Wim]

Hi Luke ... thanks so much for your response.

I copied your code but if gives the same error.

The worksheets actually have names which is not "SheetX". I changed Sheet5 to "Details" and Sheet4 to "Summary" but it still give the same error.

The one sheet was also hidden which I unhide thinking it might be the problem but it was not.

Debugging highlights this line

With Worksheets("Details")

 

[Luke M]

Do you have multiple workbooks open? Is it somehow possible that the workbook with data of interest is not the active one?? (seems very unlikely...)

Check the sheet names, make sures there's no odd space (it's not really named "Details ")

 

[Wim]

Yes I have quite a few workbooks open ... but I would think the one I am working on is the active one. But I will close them and also check the names carefully. When naming the sheets it happens quick without thinking much and one could have added a space by accident. (That's why I used "Sheet" initially thinking one need to refer to the source .. obviously not!) I am a little clue-less on the subject but like the way it makes life easier when you know what to do ...

Thanks for your help

 

[Wim]

I checked the names and it seems to be right. But after I did fiddle with the names the macro worked.
New problem discovered is that the ID's I am trying to copy is actually a concatenation of two rows =Value(A2&B2) ... need to figure out ...

 

[Wim]

How do one change the code so it copies values and not the formula?

I tried to fit in PasteSpecial xlPasteValues but does not get it right ...

 

[Luke M]

Sub UniqueListAcrossSheets()

Dim ULR As Long
Dim rSrc As Range

With Worksheets("Sheet5")
    ULR = .Cells(.Rows.Count, "C").End(xlUp).Row
    Set rSrc = .Range("C2:C" & ULR)
End With
rSrc.Copy 
Worksheets("Sheet4").Range("A4").PasteSpecial xlPasteValues
Application.CutCopyMode = False

End Sub

 

[Wim]

Thanks Luke! youre a star