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

Tue, 03 Apr 2007 09:43:44 Z

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

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

Tue, 03 Apr 2007 11:16:45 Z

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

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

Tue, 03 Apr 2007 12:50:22 Z

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