I get two copies of pictures in the loop instead of one - why?

[Maria High]

Hi,

I have a sheet where I in Worksheet_Activate copy all chartobjects and paste
them as pictures and then hide the chartobjects. This make the users not
able to ruin the references in the charts.

Problem: I get two copies of pictures...why????

She sheet1 had to have a couple of charts. Code:

Sub CopyChart()
Dim chtobj As ChartObject
Dim W As Double
Dim H As Double
Dim T As Double
Dim L As Double

ActiveSheet.Unprotect
ActiveSheet.Pictures.Delete

For Each chtobj In Sheet1.ChartObjects
W = chtobj.Width
H = chtobj.Height
L = chtobj.Left
T = chtobj.Top
chtobj.CopyPicture Appearance:=xlScreen, Format:=xlPicture

ActiveSheet.Pictures.Paste.Select
With Selection
.Width = W
.Height = H
.Left = L
.Top = T
End With
Next chtobj

ActiveSheet.ChartObjects.Visible = False
Exit Sub

/Regards

 

[Tom Ogilvy]

What triggers your macro? Maybe you are running it twice.

 

[Maria High]

Good point, but I don't think that's the problem. Actually, I let the
Worksheet_Activate Sub call the "CopyChart" Sub, but I have a
EnableEvent=False before so it shouldn't happen two times beacause of that.

When I pace through the code in debugging mode, it just seem continue
pasting in double...could it be that it continue to copy paste the pictures
it just has created? Stupid of it, in that case, within the same Each...Next
loop!

When pacing through, I can't see if it actually copy the new created
pictures or the chartobjects.

/Regards

 

[Tom Ogilvy]

I ran your code and it only created one copy of each chartobject. I ran it
again and same result. Only one copy of each chart object.

xl97.