title: Copying a union range with a last row value
body: <p>I have the following code that is sort of working now but I've hit another snag. For some reason it is copying from the same sheet multiple times and pasting to the new workbook. I think this is due to my <code>NumRange + 100</code> perimeter I set because I couldnt get the LastRow variable to work.</p>
  
  <pre><code>Public Sub extractCol()
  
  'Find FF&amp;E Section, Add 3 rows and Identify relevant columns.
  
  Dim rFind As Range, CRange As String, ERange As String, KRange As String, MRange As String
  Dim ws As Worksheet
  Dim NewBook As Workbook
  Dim NumRange As Long
  Dim LastRow As Long
  
  Set NewBook = Workbooks.Add ' Open new Workbook
  
  For Each ws In ThisWorkbook.Worksheets
  With ws
      Set rFind = .Range("A:A").Find(What:="FF&amp;E", LookAt:=xlWhole,     MatchCase:=False, SearchFormat:=False)
      If Not rFind Is Nothing Then
  
          LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
          NumRange = rFind.Row + 3 ' Find FF&amp;E line and add three
  
          CRange = "C" &amp; NumRange &amp; ":" &amp; "C" &amp; NumRange + 100 ' Define First 100 Lines in Column C
          ERange = "E" &amp; NumRange &amp; ":" &amp; "E" &amp; NumRange + 100 ' Define First 100 Lines in Column E
          KRange = "K" &amp; NumRange &amp; ":" &amp; "K" &amp; NumRange + 100 ' Define First 100 Lines in Column K
          MRange = "M" &amp; NumRange &amp; ":" &amp; "M" &amp; NumRange + 100 ' Define First 100 Lines in Column M
  
          Set range1 = Union(.Range(CRange), .Range(ERange), .Range(KRange), .Range(MRange)) ' Combine individual column ranges in to one selection
          range1.Copy ' Copy new combined range
  
          NewBook.Sheets(1).Range("B" &amp; Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues ' Paste to new Workbook
          NewBook.Sheets(1).Range("A" &amp; Rows.Count).End(xlUp)(2).Resize(range1.Rows.Count).Value = FindRoom(ws)
  
      End If
  End With
  Next ws
  
  End Sub
  
  Function FindRoom(ws As Worksheet)
  
  shtName = ws.Name
  
  Dim arr() As String
  arr = VBA.Split(shtName, " ")
  
  xCount = UBound(arr)
  If xCount &lt; 1 Then
      FindRoom = "Unknown"
  Else
      FindRoom = arr(xCount)
  End If
  End Function
  </code></pre>
  
  <p>So I tried fixing it by doing the following;</p>
  
  <pre><code>            CRange = "C" &amp; NumRange &amp; ":" &amp; "C" &amp; LastRow
              ERange = "E" &amp; NumRange &amp; ":" &amp; "E" &amp; LastRow
              KRange = "K" &amp; NumRange &amp; ":" &amp; "K" &amp; LastRow
              MRange = "M" &amp; NumRange &amp; ":" &amp; "M" &amp; LastRow
  </code></pre>
  
  <p>But this causes an error on the following line;</p>
  
  <pre><code>range1.Copy
  </code></pre>
  
  <p>Any help on where I'm going wrong would be appreciated.
  Thanks!</p>