title: Use the text of a cell as the argument of range() with structured references
body: <p>I want to execute an advanced filter with vba. The problematic step is that the name of the source table (for the advanced filter) is written in a cell and it seems I can't find a way to pass it to <code>range()</code>.</p>
  
  <p>Here is the idea:</p>
  
  <p><a href="https://i.stack.imgur.com/4MrHQ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/4MrHQ.png" alt="A table with date and places resulting from an advanced filter. A cell contain the name of the source table, here it is DatePlace."></a></p>
  
  <p>The F5 cell contain "DatePlace" and it is the name of the table that should be used in the advanced filter. By recording a macro I saw that we could use structured references in VBA but when I try to take the reference from the value of F5, the advanced filter doesn't execute anymore.</p>
  
  <p>I receive this error message box (translated from french) :</p>
  
  <pre><code>Execution error '1004':
  Error defined by the application or by the object
  </code></pre>
  
  <p>Here is one of my attempts :</p>
  
  <pre><code>Worksheets("Planning")
  .Range(Worksheets("Macro").Range("$F$5").Text &amp; "[[#Headers],[#Data]]")
  .AdvancedFilter Action _:=xlFilterCopy, 
  CriteriaRange:=Worksheets("Planning").Range("E7:F8"), 
  CopyToRange:=Worksheets("Planning").Range( _"E11:F11"), Unique:=False
  </code></pre>
  
  <p>I tried with <code>.Value</code> instead of <code>.Text</code>. I tried to pass it with a string variable too. I also tried to write the full reference in the F5 cell. And when I tried to get a range variable I just moved the issue to that variable initiation.</p>
  
  <p>Any help is welcomed.</p>
  
  <p>Edit 1 : 
  This way of doing it works but I have to hard code the structured reference and that's exactly what I want to avoid.</p>
  
  <pre><code>Worksheets("Planning")
  .Range("DateLieu[[#Headers],[#Data]]")
  .AdvancedFilter Action _:=xlFilterCopy, 
  CriteriaRange:=Range("A6:B7"), 
  CopyToRange:=Range( _"A10:B10"), Unique:=False
  </code></pre>
  
  <p>Edit 2 :
  It should have worked without issue. Here is how the code looks now.</p>
  
  <pre><code>' Variables
  Dim rngPlanning As String
  
  ' Initiations
  rngPlanning = ThisWorkbook.Worksheets("Macro").Range("$F$5").Text &amp; "[[#Headers],[#Data]]"
  
  ' Actions
      Application.CutCopyMode = False
      Debug.Print (rngPlanning)
      Worksheets("Planning").Range(rngPlanning).AdvancedFilter _
          Action:=xlFilterCopy, _
          CriteriaRange:=Worksheets("Macro").Range("$E$7:$F$8"), _
          CopyToRange:=Worksheets("Macro").Range("$E$11:$F$11"), _
          Unique:=False
  </code></pre>