Results 1 to 7 of 7
  1. #1
    Join Date
    May 2010
    Posts
    8

    Unanswered: Finding the correct worksheet---Help!

    Hi,

    I am trying to write a marco that allows me to have a list of names and find the correct worsksheet tab.

    Let me be more specific.

    I have created a drop down function that is connected to a list (The list being all the names of my worksheet tabs). I have then created a lookup function that shows the particular worksheet I would like to analyze. What I need is for the macro to recognize that name and open the correct worksheet.

    Thanks

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    At this stage I can't be more specific than:
    Code:
    Worksheets(DropDownListRange.Value)....etc.....
    Where DropDownListRange is a reference to the range containing the dropdown list.

    Does that help?

  3. #3
    Join Date
    May 2010
    Posts
    8
    It is saying that "worksheets" is an invald use of properties.

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Post the code you're using? I assume this is in Excel VBA?

  5. #5
    Join Date
    May 2010
    Posts
    8
    This is my code so far. It is updating two queries, then brining me back to my home page. From there I want my drop down list which the cell link is m3. On cell m4 I have a vlookup which says the same (ex."Avg Daily Gain"). From there I would like my macro to take the cell link (m3 or if need the entire name m4) and open the same named worksheet.

    Sheets("North Data").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Sheets("South Data").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Sheets("Criteria").Select
    Worksheets (dropdownlistrange.Value)

  6. #6
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    I think this is what you are describing you want:

    Code:
    Sheets("North Data").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Sheets("South Data").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
     
    Worksheets(Worksheets("Criteria").Range("M4").Value).Select
    I'm assuming that Criteria is the name of your 'home page' and that the cell Criteria!M4 contains a valid worksheet name. I'm also assuming that you want to select/activate the worksheet specified in Criteria!M4. If your 'home page' is named something else then you'll need to amend the string in red accordingly.

    Hope that helps...

  7. #7
    Join Date
    May 2010
    Posts
    8
    Sweet! That is exactly what I was looking for! Thanks!!!!

Posting Permissions

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