title: VBA trying to use range.offset with a cell address held in a named variable
body: <p>I am trying to get a small matrix of data from one workbook and paste it into ThisWorkbook using offset from a cell reference. The cell reference changes though as I'm cycling down a range looking for values. The variable I've set called 'WhereCell' holds the address of the currently held value, and I want to offset from whatever is held in WhereCell. It works if I hard code a cell reference, eg Range("A1") but with the code as it is I get a 1004: Method 'range' of object '_Global' failed. </p>
  
  <p>How can I change it to offset from 'WhereCell'?</p>
  
  <pre><code>Sub GetFlows()
  
  Dim rng As Range
  Dim row As Range
  Dim cell As Range
  Dim dem1 As String
  Dim WhereCell As Range
  Dim valueRng As Range
  Dim x As Long
  Dim y As Long
  
  Set rng = Range("A9:A200")
  
  For x = 1 To rng.Rows.Count
  
  dem1 = rng.Cells(x).Value
  
  If dem1 &lt;&gt; "" Then
      Set WhereCell = ThisWorkbook.ActiveSheet.Range("A9:A200").Find(dem1, lookat:=xlPart)
      Windows("GetFilenames v2.xlsm").Activate
      Worksheets(dem1).Range("A1").CurrentRegion.Copy
      ThisWorkbook.ActiveSheet.Range(Range(WhereCell), Range(WhereCell).Offset(, 2)).PasteSpecial Paste:=xlPasteValues
      'MsgBox dem1
      'If Not WhereCell Is Nothing Then
      'MsgBox WhereCell.Address
      'End If
      Else
      ThisWorkbook.Activate
  End If
  
  Next x
  
  End Sub
  </code></pre>