VBA to copy unique values list to different sheets

[Jed Shields]

Hi guys,

I've got a few answers from another thread, but I need some further help, plus I didn't want to hijack that thread:

http://www.mrexcel.com/forum/showthread.php?p=2855000#post2855000

I've got list of users that is currently around 10,000 lines, of which around 1500 are unique. I need to copy a list of the unique names to a number of different sheets. The first part that copies the unique list to the current sheet works fine:

Sub EasySol()
Dim LR As Long
LR = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:A" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("L1"), Unique:=True

End Sub

Sub EasySol2()

Dim ULR As Long
ULR = ActiveSheet.Cells(Rows.Count, "L").End(xlUp).Row
Range("L1:L" & ULR).Select
Selection.Copy
Rem Sheets("Sheet2").Select
Range("m1:m" & ULR).Select
ActiveSheet.Paste
Rem Sheets("Sheet3").Select
Range("n1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("A1").Select
End Sub

I've rem'd the sheet change code out as this is where it falls over. When I run this code it copies the unique list to rows L, M and N in the same sheet. I've also played around with the range where the unique list goes ie:

Range("L1:L" & ULR).Select
&
Range("n1").Select

Both appear to work okay.

The trouble I'm having is trying to copy the list to Sheet2!M1 and Sheet3!N1. I keep getting errors.

Any ideas?

I'm also looking for a bit of code that will copy a lookup formula in the row next to the unique list ... however, I need to sort the different sheets part out first

Cheers,

Jed.

 

[Andrew Poulsom]

To copy from column L on the active sheet to column M on Sheet2:

ActiveSheet.Range("L1:L" & ULR).Copy Sheets("Sheet2").Range("M1")

 

[Jed Shields]

Great, that bit works perfectly!

Any ideas on copying a formula down for each entry in column next to the unique lists?

 

[pgc01]

Hi Jed
Welcome to the board


To copy the values in column L in Sheet1 to column M in Sheet2 and column N in Sheet3. Try:

Sub EasySol2()
Dim ULR As Long
Dim rSrc As Range
 
ULR = Worksheets("Sheet1").Cells(Rows.Count, "L").End(xlUp).Row
Set rSrc = Worksheets("Sheet1").Range("L1:L" & ULR)
 
rSrc.Copy Destination:=Worksheets("Sheet2").Range("M1")
rSrc.Copy Destination:=Worksheets("Sheet3").Range("N1")
End Sub

Remark: You don't usually Select objects in vba. Its unnecessary, inefficient and confusing.

 

[Andrew Poulsom]

Where is the formula you want to copy?

 

[Jed Shields]

Thanks PGC, I'll take a look now.

I think the Select came from when I recorded a macro to do what I wanted before I tried modifying it. I'll trim the fat from the code

EDIT: the formula is a simple lookup that will retrieve the users phone number. It's currently on Sheet 1 M2:1500. The unique list will only get bigger and I need the phone number for any new user added to the list. What's the best way of doing this?

 

[Jed Shields]

Well, with the help of you guys, Google and a bit of good old fashioned messing about, I've finally got it working:

Sub UniqueList()
Dim LR As Long
 LR = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
     Range("A1:A" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("L1"), Unique:=True
        
End Sub


Sub UniqueListAcrossSheets()

Dim ULR As Long
Dim rSrc As Range
 
ULR = Worksheets("Sheet1").Cells(Rows.Count, "L").End(xlUp).Row
Set rSrc = Worksheets("Sheet1").Range("L1:L" & ULR)
 
rSrc.Copy Destination:=Worksheets("Sheet2").Range("M1")
rSrc.Copy Destination:=Worksheets("Sheet3").Range("N1")
End Sub

Sub CopyFormulaDown()

Dim lngLastrow As Long
Dim rngTargetStart As Range
Dim rngTargetEnd As Range


lngLastrow = Worksheets("Sheet1").Range("L65536").End(xlUp).Cells(1, 1).Row

Set rngTargetStart = Worksheets("Sheet1").Range("M2") ' for my example, the formula is stored in M2
Set rngTargetEnd = Worksheets("Sheet1").Cells(lngLastrow, 13) ' last row, column M

Worksheets("Sheet1").Range("M2").Copy Range(rngTargetStart, rngTargetEnd)


End Sub

Sub UniqueList() copies the unique values from the huge list in Column A over to Column L.

Sub UniqueListAcrossSheets() copies the same list onto different worksheets.

Sub CopyFormulaDown() copies the formula in M2 down as far as the last unique list item in Column L.

Next step is to try to standardise the syntax - the last step uses a different referencing syntax for the range than in the first two.

And more importantly, I figured out how to get the code into the easy to read box

 

[pgc01]

I'm glad it helped. Thanks for the syntax.

 

[Jed Shields]

I'm on a roll now, I've managed to alter the code so that I can copy a formula down and across to fill up all the cells between B2 and T100, dependant on a similar unique user list for a different spreadsheet. This is going to be really useful

 

[Jed Shields]

Back again!

I've got a number of different codes working fine and I'd like to add them to a button that will run them all at the same time. I've added the button to a different sheet (Macro) and have added the following code:

Private Sub CommandButton1_Click()

Call UniqueUserList
Call UniqueUserListToLookupSheet
Call CopyGUDPhoneNoFormulaDown

End Sub

UniqueUserList code is on Sheet OrangeData

The other two are on Sheet Lookup.

When I click on the button I get an error - "Compile Error: Sub or Function not defined".

Can I only add code to a button that's on the same sheet?