Scott Boyd

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
'copy manager from sheet 1
'paste into sheet 2
End If


End Sub

Its bringing up an error on the highlighted line above.

Any help is great thanks Smile

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


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

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

Scott Boyd

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