title: Dynamic Depending Lists in Separated WorkSheets in VBA
body: <p>I'm not really expert in VBA and I have a problem with my code and I don't know how to solve it. (The code is from: <a href="http://siddharthrout.wordpress.com/2011/07/29/excel-data-validationcreate-dynamic-dependent-lists-vba/" rel="nofollow">http://siddharthrout.wordpress.com/2011/07/29/excel-data-validationcreate-dynamic-dependent-lists-vba/</a>)</p>
  
  <p>I'm working with 8 dynamic dependent lists, and I thought the best way to automate the process and avoid to modify the macro in a future if I modify the lists was a VBA code.</p>
  
  <p>Trying to find the correct code, I'm just working with to lists. For after, apply it for all lists.</p>
  
  <p>I've checked the code and I discovered that there's an error (method 'intersect' of object '_global' failed) because I'm comparing two ranges from a different worksheets.</p>
  
  <p>My code is:</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
  
  Dim i As Long, LastRow As Long, n As Long
  Dim MyCol As Collection
  Dim SearchString As String, Templist As String
  
  Application.EnableEvents = False
  
  On Error GoTo Whoa
  
  ' Find LastRow in Col A
  LastRow = Sheet2.Range("A" &amp; Rows.Count).End(xlUp).Row
  
  If Not Intersect(Target, Sheet2.Columns(1)) Is Nothing Then
  Set MyCol = New Collection
  
  ' Get the data from Col A into a collection
  For i = 2 To LastRow
      If Len(Trim(Sheet2.Range("A" &amp; i).Value)) &lt;&gt; 0 Then
          On Error Resume Next
          MyCol.Add CStr(Sheet2.Range("A" &amp; i).Value), CStr(Sheet2.Range("A" &amp; i).Value)
          On Error GoTo 0
      End If
  Next i
  
  ' Create a list for the Data Validation List
  For n = 1 To MyCol.Count
      Templist = Templist &amp; "," &amp; MyCol(n)
  Next
  
  Templist = Mid(Templist, 2)
  
  Range("A2").ClearContents: Range("A2").Validation.Delete
  
  ' Create the Data Validation List
  If Len(Trim(Templist)) &lt;&gt; 0 Then
      With Range("A2").Validation
          .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Templist
          .IgnoreBlank = True
          .InCellDropdown = True
          .InputTitle = ""
          .ErrorTitle = ""
          .InputMessage = ""
          .ErrorMessage = ""
          .ShowInput = True
          .ShowError = True
      End With
  End If
  
  ' Capturing change in cell A2
  ElseIf Not Intersect(Target, Range("A2")) Is Nothing Then
  SearchString = Range("A2").Value
  
  Templist = FindRange(Sheet2.Range("A2:A" &amp; LastRow), SearchString)
  
  Range("B2").ClearContents: Range("B2").Validation.Delete
  
  If Len(Trim(Templist)) &lt;&gt; 0 Then
      ' Create the DV List
      With Range("B2").Validation
          .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Templist
          .IgnoreBlank = True
          .InCellDropdown = True
          .InputTitle = ""
          .ErrorTitle = ""
          .InputMessage = ""
          .ErrorMessage = ""
          .ShowInput = True
          .ShowError = True
      End With
  End If
  End If
  LetsContinue:
  Application.EnableEvents = True
  Exit Sub
  Whoa:
  MsgBox Err.Description
  Resume LetsContinue
  End Sub
  
  ' Function required to find the list from Col B
  Function FindRange(FirstRange As Range, StrSearch As String) As String
  Dim aCell As Range, bCell As Range, oRange As Range
  Dim ExitLoop As Boolean
  Dim strTemp As String
  
  Set aCell = FirstRange.Find(what:=StrSearch, LookIn:=xlValues, lookat:=xlWhole, SearchOrder:= _
  xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
  
  ExitLoop = False
  
  If Not aCell Is Nothing Then
      Set bCell = aCell
      strTemp = strTemp &amp; "," &amp; aCell.Offset(, 1).Value
      Do While ExitLoop = False
          Set aCell = FirstRange.FindNext(After:=aCell)
  
          If Not aCell Is Nothing Then
              If aCell.Address = bCell.Address Then Exit Do
              strTemp = strTemp &amp; "," &amp; aCell.Offset(, 1).Value
          Else
              ExitLoop = True
          End If
      Loop
      FindRange = Mid(strTemp, 2)
  End If
  End Function
  </code></pre>
  
  <p>Into the Sheet1, I just want the cells to select the list options and into the Sheet2, I want the all dynamic and dependent lists.</p>
  
  <p>Is there any possibility to compare two ranges from a different worksheets using these algorithm? Or an alternative code to create a pick list for 8 depending and dynamic lists?</p>