title: Compile Error: Method 'Range' of object '_Global' failed - Search Copy Paste Macro Excel VBA
body: <p>I'm trying to make a macro in Excel VBA 2007 that searches through the selected field and if it finds a certain string anywhere in a row, it copies and pastes that row into another sheet.</p>
  
  <p>However, I'm getting the error in the title on the row noted below. What would be causing this?</p>
  
  <pre><code>Sub SearchCopyPaste()
  '
  ' SearchCopyPaste Macro
  ' Searches for a string. If it finds that string in the line of a document then it copies and pastes it into a new worksheet.
  '
  ' Keyboard Shortcut: Ctrl+Shift+W
  '
  
      Dim sourceSheet, destinationSheet As Worksheet
      Set sourceSheet = Worksheets(1)               'Define worksheets
      Set destinationSheet = Worksheets(2)
  
      Dim selectedRange As Range                    'Define source range
      Set selectedRange = Selection
  
      Dim numRows, numColumns As Integer                            'Determine how many rows and columns are to be searched
      numRows = Range(selectedRange).Rows.Count '&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt; Error
      numColumns = Range(selectedRange).Columns.Count
  
      destinationRowCount = 1                     'Counter to see how many lines have been copied already
                                                      'Used to not overwrite, can be modified to add header,etc
  
      Dim searchString As String                      'String that will be searched. Will eventually be inputted
      searchString = "bccs"                       'Will eventually be put into msgbox
  
      For rowNumber = 1 To numRows
          If InStr(1, selectedRange.Cells(i, numColumns), searchString) &gt; 0 Then
              selectedRange.Cells(rowNumber, numColumns).Copy Destination:=destinationSheet.Range(Cells(destinationRowCount, numColumns))
              destinationRowCount = destinationRowCount + 1
          End If
      Next rowNumber
  
  End Sub
  </code></pre>