Location method with ActiveSheet

Hello everyone,
I am struggling with a small problem, where I believe that I don't exactly understand the activeSheet property.

I have created a stacked columns chart in vba which works perfectly. It takes data from the sheet I am in and adds a chart below the table. I locate the newly created chart using this code:

Set chtChart  = chtChart.Location (Where:=xlLocationAsObject, Name:="Waterfall")

The problem with this is that it refers to a named sheet. If I want to change the name of the sheet, I would have to change the code. I just want the code to refer to the name of the active sheet I am in.

Having failed to use ActiveSheet.Name for the parameter Name:=, I do not know where to go from here. Any help would be appreciated. Thank you!

Originally Posted by blane245

You may be having trouble keeping track of which sheet is actually active. If you are activating various sheets, then the active sheet changes. I suggest that you set a worksheet variable to the activesheet early in the procedure and use that variable to provide the name.
For example, the code below will move the chart to Sheet1 if it is active when the procedure is run and will move it to Sheet2 is it is active when the procedure is run.

Sub doit()
Dim a As Chart
Dim b As Worksheet
Set b = ActiveSheet
Set a = Charts.Add
Set a = a.Location(xlLocationAsObject, b.Name)
End Sub

Thank you for your help. Here is what I did to solve the problem:
- I set a new variable as my ActiveSheet as shown above and I was then able to use the code for the location of the file above.
- I was using "Where:=" to name the location, but not "Name:=" for the name. This would not work:

Set a = a.Location(Where:=xlLocationAsObject, b.Name)

- I then just got rid of the definition of the parameter and it worked just fine:

Set a = a.Location(xlLocationAsObject, b.Name)

I did not try the codename solution, which could be useful in other cases, but my code used the ActiveSheet extensively, so I just went for the first suggestion. Thanks again!