VBA trying to use range.offset with a cell address held in a named variable

Question

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.

How can I change it to offset from 'WhereCell'?

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 <> "" 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

Answer 1

Try like this:

WhereCell.Offset(0, 2)).PasteSpecial Paste:=xlPasteValues

instead of this:

ThisWorkbook.ActiveSheet.Range(Range(WhereCell), Range(WhereCell).Offset(, 2)).PasteSpec...

To understand more about WhereCell, consider writing

Debug.Print WhereCell.Parent.Name
Debug.Print WhereCell.Parent.Parent.Name
Debug.Print WhereCell.Address

after declaring it. It will show you its Worksheet, the name of the Application and its address in the immediate window. Thus, it already "knows" them and if you try to "redeclare" them, you get an error.