title: VBA Run time error '424' Object Required issue
body: <p>I'm trying to pass two sheets to a different subroutine in Excel VBA to make some manipulations to those sheets.  Ultimately, I'm trying to combine data from multiple sheets and delete all of the duplicate data found in each list.  I'm defining as an object:</p>
  
  <pre><code>    Set wb1 = Workbooks.Open(Pathname &amp; Filename)
      Set newWB = Workbooks.Add
  </code></pre>
  
  <p>Then I'm just trying to a function:</p>
  
  <pre><code>    Call ThisSubroutine(wb1.Sheets("Sheetnumber1"), newWB.Sheets("Sheet2"))
  </code></pre>
  
  <p>And I'm getting a run-time error '424' Object Required dialog box.  I'm sure there is an obvious solution here, but I'm overlooking something.  The sub is written:</p>
  
  <pre><code>Sub ThisSubroutine(Sourcefile As Worksheet, Targetfile As Worksheet)
  </code></pre>
  
  <p>On request, I'm adding the whole code:</p>
  
  <pre><code>Sub MergeDuplicates(ByVal DuplicateFilename As String) 'used ByVal because I was getting a "ByRef argument type mismatch" error; don't know why this happens with Dir function, as it should be passing a string, but this seems to fix it, at least as far as compiling the CheckDuplicates Sub
      'This one is a bit tricky, but I think the best way to do this is:
      'open the original and the duplicate copy (find partial string matches and open both files)
      Pathname = "\\SRVWIN0791\Daniel_Armstrong$\TM Database Company Files\" 'for now... everything stays in my U drive
      Set wb1 = Application.Workbooks.Open(Pathname &amp; DuplicateFilename)
      Dim Partialname As String
      File = Dir(Pathname)
      Partialname = Left(DuplicateFilename, 4)
      Do While File &lt;&gt; ""
          If StrComp(Left(File, 4), Partialname) = 0 Then
              Set wb2 = Workbooks.Open(Pathname &amp; File)
          End If
          File = Dir()
      Loop
  
      'Create a new workbook, creates new sheets and name them
      Set newWB = Workbooks.Add
      For i = 1 To 6
          newWB.Worksheets.Add After:=newWB.Sheets(newWB.Sheets.Count)
      Next i
  
      'copy the contents of both workbooks into the new one keeping everything on the appropriate sheets
      Call CopyToNewTMWB(wb1.Sheets("General Information"), newWB.Sheets("Sheet2"))
      Call CopyToNewTMWB(wb1.Sheets("Markets"), newWB.Sheets("Sheet3"))
      Call CopyToNewTMWB(wb1.Sheets("Chemistries"), newWB.Sheets("Sheet4"))
      Call CopyToNewTMWB(wb1.Sheets("Processing Capabilities"), newWB.Sheets("Sheet5"))
      Call CopyToNewTMWB(wb1.Sheets("Equipment List"), newWB.Sheets("Sheet6")) 'Wrong, should not be using this function here
      Call CopyToNewTMWB(wb1.Sheets("Analytical &amp; QC"), newWB.Sheets("Sheet7"))
      Call CopyToNewTMWB(wb1.Sheets("Utilities"), newWB.Sheets("Sheet8"))
      Call CopyToNewTMWB(wb1.Sheets("Stock Chemicals"), newWB.Sheets("Sheet9"))
  
      newWB.Sheets("Sheet2").Name = "General Information"
      newWB.Sheets("Sheet3").Name = "Markets"
      newWB.Sheets("Sheet4").Name = "Chemistries"
      newWB.Sheets("Sheet5").Name = "Processing Capabilities"
      newWB.Sheets("Sheet6").Name = "Equipment List"
      newWB.Sheets("Sheet7").Name = "Analytical &amp; QC"
      newWB.Sheets("Sheet8").Name = "Utilities"
      newWB.Sheets("Sheet9").Name = "Stock Chemicals"
  
      Call AddToNewTMWB(wb2.Sheets("General Information"), newWB.Sheets("General Information"))
      Call AddToNewTMWB(wb2.Sheets("Markets"), newWB.Sheets("Markets"))
      Call AddToNewTMWB(wb2.Sheets("Chemistries"), newWB.Sheets("Chemistries"))
      Call AddToNewTMWB(wb2.Sheets("Processing Capabilities"), newWB.Sheets("Processing Capabilities"))
      Call AddToNewTMWB(wb2.Sheets("Equipment List"), newWB.Sheets("Equipment List")) 'Wrong.... should not be using this function for this purpose
      Call AddToNewTMWB(wb2.Sheets("Analytical &amp; QC"), newWB.Sheets("Analytical &amp; QC"))
      Call AddToNewTMWB(wb2.Sheets("Utilities"), newWB.Sheets("Utilities"))
      Call AddToNewTMWB(wb2.Sheets("Stock Chemicals"), newWB.Sheets("Stock Chemicals"))
  
      'use excel's built in "remove duplicates" functions on each list
      Sheet3.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo
      Sheet3.Range("B:B").RemoveDuplicates Columns:=1, Header:=xlNo
      Sheet4.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo
      Sheet4.Range("B:B").RemoveDuplicates Columns:=1, Header:=xlNo
      Sheet4.Range("D:D").RemoveDuplicates Columns:=1, Header:=xlNo
      Sheet5.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo
      Sheet5.Range("B:B").RemoveDuplicates Columns:=1, Header:=xlNo
      'This is tricky.... not sure how to handle because there might be minor changes; maybe just don't include it at all...?
      Sheet6.Range("A:Z").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24), _
          Header:=xlYes
      Sheet7.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo
      Sheet7.Range("B:B").RemoveDuplicates Columns:=1, Header:=xlNo
      Sheet8.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo
      Sheet8.Range("B:B").RemoveDuplicates Columns:=1, Header:=xlNo
      Sheet9.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo
      Sheet9.Range("B:B").RemoveDuplicates Columns:=1, Header:=xlNo
  
      'for general information and the equipment list, this is going to be a bit trickier, because the duplicates
          'on the equipment list require matching for all 20-some-odd rows and the general information may be actual updates so
          'how can I decide what information to update?
  
      'save the old workbooks as "Company Name &amp; City &amp; Date &amp; Old" and "Company Name &amp; City &amp; Date &amp; Duplicate" in a different folder!!!!!
      wb1.SaveAs filename:="\\SRVWIN0791\Daniel_Armstrong$\TM Duplicate Files\" &amp; DuplicateFilename
      wb2.SaveAs filename:="\\SRVWIN0791\Daniel_Armstrong$\TM Duplicate Files\" &amp; "Merge " &amp; Format(Date, "dd-mm-yy") &amp; " " &amp; File
  
      'save the newly created workbook as "Company Name &amp; City" in \\SRVWIN0791\Daniel_Armstrong$\TM Database Company Files\
      newWB.SaveAs filename:=Pathname &amp; File
  
      'Delete the old files from the "TM Database Company Files" folder
  
  End Sub
  
  Sub CopyToNewTMWB(SourceSheet As Worksheet, TargetSheet As Worksheet)
  
      Dim numRows As Integer, numCols As Integer
      Dim ActiveRangeOld As Range, ActiveRangeNew As Range
  
      'count cells to define active range
      numRows = SourceSheet.Cells(Rows.Count, 1).End(xlUp).Row
      numCols = SourceSheet.Cells(1, Columns.Count).End(xlToLeft).Column
      Set ActiveRangeOld = SourceSheet.Range(SourceSheet.Cells(1, 1), SourceSheet.Cells(numRows, numCols)) 'set active range equal to appropriate size
  
      Set ActiveRangeNew = TargetSheet.Range(TargetSheet.Cells(1, 1), TargetSheet.Cells(numRows, numCols)) 'choose range on new worksheet of same size as above
      ActiveRangeNew.Value = ActiveRangeOld.Value 'set the new range values equal to the old ones without having to select any cells
  
  End Sub
  
  Sub AddToNewTMWB(ByVal SourceSheet As Worksheet, ByVal TargetSheet As Worksheet) 'slightly different, just copies the cells to the first unused location
  
      Dim numRows As Integer, numCols As Integer
      Dim ActiveRangeOld As Range, ActiveRangeNew As Range
  
      'count cells to define active range
      numRows1 = SourceSheet.Cells(Rows.Count, 1).End(xlUp).Row
      numRows2 = SourceSheet.Cells(Rows.Count, 2).End(xlUp).Row
      numRowTarget1 = TargetSheet.Cells(Rows.Count, 1).End(xlUp).Row
      numRowTarget2 = TargetSheet.Cells(Rows.Count, 2).End(xlUp).Row
      'write duplicates at end of existing list for new worksheet
      Set ActiveRangeOld = SourceSheet.Range(SourceSheet.Cells(1, 1), SourceSheet.Cells(numRows1, 1)) 'set active range equal to appropriate size in first column
      Set ActiveRangeNew = TargetSheet.Range(TargetSheet.Cells(numRowTarget1 + 1, 1), TargetSheet.Cells(numRowTarget1 + numRows1 + 1, 1)) 'choose range on new worksheet of same size as above
      ActiveRangeNew.Value = ActiveRangeOld.Value 'set the new range values equal to the old ones without having to select any cells
      'repeat for 2nd column
      Set ActiveRangeOld = SourceSheet.Range(SourceSheet.Cells(1, 2), SourceSheet.Cells(numRows1, 2)) 'set active range equal to appropriate size in first column
      Set ActiveRangeNew = TargetSheet.Range(TargetSheet.Cells(numRowTarget1 + 1, 2), TargetSheet.Cells(numRowTarget1 + numRows1 + 1, 2)) 'choose range on new worksheet of same size as above
      ActiveRangeNew.Value = ActiveRangeOld.Value 'set the new range values equal to the old ones without having to select any cells
  
  End Sub
  </code></pre>