0

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 range().

Here is the idea:

A table with date and places resulting from an advanced filter. A cell contain the name of the source table, here it is DatePlace.

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.

I receive this error message box (translated from french) :

Execution error '1004':
Error defined by the application or by the object

Here is one of my attempts :

Worksheets("Planning")
.Range(Worksheets("Macro").Range("$F$5").Text & "[[#Headers],[#Data]]")
.AdvancedFilter Action _:=xlFilterCopy, 
CriteriaRange:=Worksheets("Planning").Range("E7:F8"), 
CopyToRange:=Worksheets("Planning").Range( _"E11:F11"), Unique:=False

I tried with .Value instead of .Text. 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.

Any help is welcomed.

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.

Worksheets("Planning")
.Range("DateLieu[[#Headers],[#Data]]")
.AdvancedFilter Action _:=xlFilterCopy, 
CriteriaRange:=Range("A6:B7"), 
CopyToRange:=Range( _"A10:B10"), Unique:=False

Edit 2 : It should have worked without issue. Here is how the code looks now.

' Variables
Dim rngPlanning As String

' Initiations
rngPlanning = ThisWorkbook.Worksheets("Macro").Range("$F$5").Text & "[[#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
Alexandre Rivara
  • 113
  • 1
  • 11
  • Note that *"doesn't work anymore"* is no useful error description. You need to tell which errors you get and where. Also specify a worksheet for **all** your ranges. The following have no worksheet specified `Range("$F$5").Text`, `Range("E7:F8")` `Range("E11:F11")` so Excel might fail guessing the correct sheet. – Pᴇʜ May 15 '20 at 07:22
  • Thank you, I tried to be more precise. – Alexandre Rivara May 15 '20 at 08:28
  • But you only added a worksheet for the first of the 3 ranges I mentioned. – Pᴇʜ May 15 '20 at 08:29
  • And furthermore, I acted too fast and referenced the wrong worksheet with the first edit. Was it mandatory to add the worksheet before all range() or was it just good practice ? – Alexandre Rivara May 15 '20 at 08:43
  • The thing is there is no "real" need (or it would throw an error) but it is highly recommended. Because if you don't do it, Excel will guess the sheet for that range and the guessing depends on where the code is written. So to make your code reliable and not depend on Excel guessing you have to specify a sheet for **all* `Range`, `Rows`, `Columns` and `Cells` objects (actually any object that can exist in a sheet). Doing so prevents a lot of issues in your code, because Excel has no space for interpretation. – Pᴇʜ May 15 '20 at 08:50
  • Did this solve the issue? – Pᴇʜ May 15 '20 at 08:51
  • No, it did not. I still have the same exact issue. But at least I won't let Excel guess anything anymore now. In the same line of thought, am I right to think that if I were to write a macro using multiple workbook I would want to specify the workbook every time. – Alexandre Rivara May 15 '20 at 08:59
  • Yes, that would be a good idea. `ThisWorkbook.Worksheets("Sheet1")` would always refer to the workbook the current line of code is written. • Are you sure that `DatePlace[[#Headers],[#Data]]` is the correct syntax and it exists in `Worksheets("Planning")`? Also check if hard coding `Worksheets("Planning").Range("DatePlace[[#Headers],[#Data]]").AdvancedFilter …` works. If not, then this is either not existing or not the correct syntax. – Pᴇʜ May 15 '20 at 09:04
  • I missed the notification, sorry. Ok, thank you, I'll pay attention to this. I edited the post and yes everything works if I give up the idea to get the reference from a cell. – Alexandre Rivara May 15 '20 at 12:01
  • Actually if hardcoding works then `Worksheets("Macro").Range("$F$5").Text & "[[#Headers],[#Data]]"` as long as `Worksheets("Macro").Range("$F$5").Text` has the correct value (no additional spaces etc), so check it's value on runtime like `Debug.Print Worksheets("Macro").Range("$F$5").Text` and check the output in the immediate window. – Pᴇʜ May 15 '20 at 12:01
  • 1
    At one step in our discussion we solved the problem but since I made another mistake (searching the criteria in the wrong worksheet) I don't know when that happened. It now works. – Alexandre Rivara May 15 '20 at 14:10

0 Answers0