Ask a question

Search Forums:

• Search Visual Basic for Applications (VBA) Forum
• Search All Microsoft ISV Community Center Forums
• Search All MSDN Forums

 

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

• Tuesday, April 03, 2007 9:43 AMScott Boyd

   
  

  0

  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  

   

  
   

  • Reply
  • Quote