Activating worksheet using both variable and text string

[linesout]

Hi all,

I've been struggling with this code and was hoping someone here could take pity on me!

Background: I have a dropdown list of categories. When a category is chosen, it is set as the variable AreaName, and used to access a worksheet whose name is the content of the variable AreaName. I then want to use table data located on this sheet to generate a chart on another sheet, whose name should be "[AreaName] Pricing". I don't want to create a new sheet every time, I'd like to activate an existing chart, wipe out its series and replace it with the series from the AreaName sheet.

When I've named sheets in the past, it lets me do Name = variable & "text string" , but for some reason this code gives me a subscript out of range error:

Please Login or Register  to view this content.

Eventually I'm hoping to turn this into a function that will be activated whenever the cell range B2 is changed, if that matters.

Also, if anyone has a less ghetto way of finding the AreaName valuable in a designated column besides the Ctrl+F function, I'm all ears

Thanks in advance,
Renee

Moderator's Edit: Use code tags in code. To do so, select your code and click on the # symbol above.

[patel45]

this code gives you a subscript out of range error if the worksheet (named AreaName & " Pricing") does not exist

[linesout]

Currently, the AreaName variable is set to the value "ERCOT". Activating Worksheet(AreaName) will activate the sheet named ERCOT. There exists a sheet, in this workbook, called "ERCOT Pricing", and the Worksheet(AreaName & " Pricing") code still gives me that error.

I've also tried it without quotes and without the space before Pricing with no effect.

[Norie]

Are you sure the name is exactly 'ERCOT Pricing'?

No trailing/leading spaces?

Where are you setting the value of AreaName?

[linesout]

Yes, the name is ERCOT Pricing exactly. What may be affecting it is that the sheet in question is a Chart?

The value of AreaName gets set earlier in the Macro, line 6 or so above.

[Norie]

Renee

A chart sheet isn't a worksheet, use Sheets instead of Worksheets.

Not sure there's a better way to find a value, what were you thinking?

[linesout]

Actually I just figured it out. It needs to be Charts(AreaName & " Pricing").Select! Didn't realize that a worksheet with only a chart in it needs to be referred to as Chart.

Thanks for your help!

[linesout]

Oh good to know I have that option also. Thanks Norie!

[Norie]

No problem.

Why are you selecting the sheet/chart anyway?

That's almost never needed in Excel VBA.

[linesout]

The task is just to display data from a table in a chart that's also stored in the workbook. Each chart will originally contain the data for all the sub-areas in it, but when the user activates a specific sub-area from a dropdown menu, the chart will be wiped blank and repopulated with the data for only that sub-area.

My understanding is that this task used to be performed with some unreliable manner of hiding rows and columns as needed, but I think this will be cleaner if you can filter from the dropdown and automate it.

Happy to hear any suggestions on how to do this in case my way is silly

[Norie]

I'm not saying it's a silly way, just that you probably don't need to have all this Select/Selection to do it.

Hard to tell what to replace it with though without seeing a workbook.