Trying to extract data from one sheet and list it in another...

Question

Hi, i have a sheet with lots of employee data on it including the manager name.  I want to loop through each employee and make a list of the managers and display this in sheet 2.  I only want each manager to be displayed once no matter how many times he occurs.

Here is what i have so far - probably very messy but its the best i can come up with!

Code Snippet

Option Explicit

Sub manager_list()

'declare all variables and initilize
Dim s1, s2 As Worksheet

Dim x As Integer
Dim Manager As String

Dim rngManager As Range
Dim c As Range

Set s1 = ThisWorkbook.Worksheets("Sheet1")
Set s2 = ThisWorkbook.Worksheets("Sheet2")

'range set to first column in sheet 2 - Manager names
Set rngManager = s2.Range(s2.Range("A2"), s2.Range("A2").End(xlDown))

Manager = "E" & x

'loop through all employees on sheet 1
For x = 2 To 1000

    'Loop through all managers on sheet 2
    For Each c In rngManager.Cells
       
        'see if manager has already been copied to sheet 2 column A
        If Range(Manager).Value = c.Value Then
           GoTo NEXT_REC
        Else
            'copy manager from sheet 1
            Range(Manager).Value.Select
            Selection.Copy
            'paste into sheet 2
            c.Select
            Selection.Paste
        End If
   
    Next
NEXT_REC:
Next

End Sub

 

Its bringing up an error on the highlighted line above. 

Any help is great thanks  

 

 

Answer 1

It would be easiest to use the AdvancedFilter method of the Range class to do this.

 

Define a range variable that represents the column of managers in your employee list (We'll call this Range1). Also define a range variable that represents a cell where you want the unique list to begin (we'll call this Range2). The latter can be on another worksheet if desired. Then the following code should do the trick:

 

Range1.AdvancedFilter xlFilterCopy, CopyToRange:=Range2, Unique:=True

 

 

Answer 2

Many thanks mate - that's a much neater and more efficient solution!