Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Feb 2011
    Location
    Washington, DC
    Posts
    87
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Table name in a variable for an advanced filter copy in an Excel 2010 macro

    To avoid duplicating lines and hardcoding references, I need to do an advanced filter copy from a table with all the parameters in variables. Recording the action in the Macro recorder gave me this:

    Range("Incident_Source_Table[All#]).AdvancedFilter AdvancedFilterAction := xlFilterCopy, CriteriaRange := Range("Y2:Y3), CopytoRange := Range("A8:AM8"), Unique :=False

    I have successfully replaced the CriteriaRange and CopytoRange ranges with string variables containing the names of ranges. I.e. CopytoRange := Range(RptCriteria) where RptCriteria holds a string that is the name of a range.

    I'm stymied on the syntax for the source range variable. I have constructed string variables whose contents are Incident_Source_Table[All#] and "Incident_Source_Table[All#]" with the quotes brought in as chr(34).

    The variables have the contents I expect, based on showing them as msgs and on looking in the debugger.

    But I always get the Runtime Error 1004 Application or Object Defined error.

    What is the right syntax for this? Is there another approach?

    Thanks,

    Jessica

  2. #2
    Lounger
    Join Date
    Aug 2014
    Posts
    40
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Give us a copy of the actual text from the macro. The text you pasted in you question is not exactly what VBA gave you. For example, the quote marks aren't matching and to assist we need to know where they are. Just use Ctrl-c to copy the text from the VBA editor and Ctrl-p to paste it into the question.

  3. #3
    Star Lounger
    Join Date
    Feb 2011
    Location
    Washington, DC
    Posts
    87
    Thanks
    5
    Thanked 5 Times in 5 Posts
    Right. I should have marked that the problem is solved. I figured out another approach.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •