loop through and rename all excel charts vba

[BuJay]

If I have a worksheet with thousands of charts, how can I get VBA to iterate through each one and rename them? For example, if the first 4 charts are currently names Chart 1001, Chart 1222, Chart 3212, and Chart 5, how do I get vba to iterate through them and rename them to Chart 1, Chart 2, Chart 3, Chart 4? I have 5000+ charts in the worksheet, so I need it to select all ChartObjects one at a time without naming referencing their current names in the loop.

Sub rename_charts()     
Dim ws As Worksheet, outputsh As Worksheet, last_cell As Range, oChartObj As Object     
'change Sheet4 to appropriate sheet name 
Set ws = ThisWorkbook.Sheets("portfolio_charts") 
Set outputsh = ThisWorkbook.Sheets("portfolio_charts") 
Sheets("portfolio_charts").Activate             
outputsh.Range("A:A").ClearContents  
If ws.ChartObjects.Count = 0 Then     
outputsh.Range("A2") = "No charts found"     
Exit Sub 
End If     

Debug.Print "Charts found: " & ws.ChartObjects.Count     

For Each oChartObj In ws.ChartObjects         
With oChartObj             
     'RENAME CHARTS HERE???         
End With     
Next      
End Sub

 

[smozgur]

Something like this? (oChartObj can be declared as ChartObject).

For Each oChartObj In ws.ChartObjects
    With oChartObj
         oChartObj.Name = "Chart " & oChartObj.Index
    End With
Next

 

[BuJay]

Something like this? (oChartObj can be declared as ChartObject).

For Each oChartObj In ws.ChartObjects
    With oChartObj
         oChartObj.Name = "Chart " & oChartObj.Index
    End With
Next

I seems like it works - but it is just taking a lot longer than I expected it to take...I tried in on a new worksheet with 4 charts and it worked but it has been running for over a half hour on the 5000+ charts on my workbook....

Thanks for the help!

 

[smozgur]

Glad to hear it helps.

it is just taking a lot longer than I expected it to take

You should try setting Application.ScreenUpdating = False right before the loop and set it back to True after the loop. It should dramatically reduce the execution time. (5000+ chart objects in a workbook, not even on a single worksheet, is beyond my imagination, but ScreenUpdating should help anyway).

 

[BuJay]

I hear you - I have about 50 charts that are the core charts - but I am continuously asked for views of the same charts across different dimensions (100 different dimensions), so I have a few codes where I can replicate the 50, change the series references, and add to the sheet.

 

[smozgur]

I hear you - I have about 50 charts that are the core charts - but I am continuously asked for views of the same charts across different dimensions (100 different dimensions), so I have a few codes where I can replicate the 50, change the series references, and add to the sheet.

Curious: Do you have to keep all of the charts? Writing a code to change the series dynamically on demand on the core charts instead of storing all of them as separate charts could be an alternative approach? (Of course, I don't know all requirements, just trying to think what my approach would be in such a situation.)

By the way, I just tested ScreenUpdating and it reduced the execution time by 50% for 1500 chart objects in my test worksheet. Just FYI.