how can i deal with a macro that copy's a sheet the charts all end up with different names?

[tonywatsonhelp]

Hi Everyone ,

I really stuck, just when I though I was making progress,
I have a macro that copys a sheet and then does a few things to the copied sheet

Sub Macro2()
   ActiveSheet.Unprotect
    Application.ScreenUpdating = False
    Sheets("Master Dashboard").Select
    Sheets("Master Dashboard").Copy After:=Sheets(5)
    ActiveSheet.Name = Range("AC7").Value
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MinimumScale = Range("EU2")
    ActiveChart.Axes(xlValue).MaximumScale = Range("EV2")



Sheets("Master Dashboard").Select
     Columns("C:Z").Hidden = True
    Columns("AE:AX").Hidden = True
  Range("AC7:AD7").ClearContents
 ActiveSheet.Protect
Application.ScreenUpdating = True
End Sub

The problem is this part
ActiveSheet.ChartObjects("Chart 1").Activate
When I copy the sheet it automaticly give the charts a new name on the new sheet, so whilst it Chart 1 in Master Dashboard tab it becomes Chart ???? in the new tab,

How can I deal with this?
is there a method of renaming the chart? i could add so the chart is given a name by the macro?
Please help if you can
Tony

 

[Worf]

Hi
Try this:

Sub Macro2()
Dim i%
ActiveSheet.Unprotect
Application.ScreenUpdating = False
Sheets("Master Dashboard").Activate
Sheets("Master Dashboard").Copy After:=Sheets(5)
ActiveSheet.Name = ActiveSheet.Range("AC7").Value
For i = 1 To ActiveSheet.ChartObjects.Count
    ActiveSheet.ChartObjects(i).Name = "Chart " & i ' rename
Next
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).MinimumScale = Range("EU2")
ActiveChart.Axes(xlValue).MaximumScale = Range("EV2")
Sheets("Master Dashboard").Activate
Columns("C:Z").Hidden = True
Columns("AE:AX").Hidden = True
Range("AC7:AD7").ClearContents
ActiveSheet.Protect
Application.ScreenUpdating = True
End Sub